Front End Developer

Search for integration logs filtered by country and endpoint, reducing data volume before applying a text search.

SELECT TOP 10
    log_id,
    country_code,
    api_endpoint,
    message_payload,
    created_at
FROM (
    SELECT
        log_id,
        country_code,
        api_endpoint,
        message_payload,
        created_at
    FROM integration_logs
    WHERE country_code = 'XX'
      AND api_endpoint = 'POST /resource'
) AS filtered_logs
WHERE message_payload LIKE CONCAT('%', :reference_id, '%')
ORDER BY created_at DESC;

Determine in which dispatch centers a product is enabled, considering product, warehouse, and business configuration rules.

SELECT
    dc.dispatch_center_id      AS dispatch_center,
    pr.product_id              AS sku_code,
    0                           AS drop_size,
    cfg.is_enabled              AS is_enabled
FROM core.dispatch_centers dc
INNER JOIN core.dispatch_products pr
    ON dc.dispatch_center_id = pr.dispatch_center_id
   AND dc.company_id = pr.company_id
INNER JOIN core.warehouses wh
    ON pr.company_id = wh.company_id
   AND pr.warehouse_id = wh.warehouse_id
   AND pr.product_id = wh.product_id
INNER JOIN core.product_config cfg
    ON cfg.product_id = pr.product_id
   AND cfg.company_id = wh.company_id
   AND cfg.warehouse_id = wh.warehouse_id
WHERE cfg.product_id IN (:sku_id)
  AND dc.company_id = :company_id
  AND cfg.config_key = 'ENABLED'
  AND dc.dispatch_center_id IN (
        SELECT DISTINCT dispatch_center
        FROM analytics.vendor_dispatch_centers
  )
ORDER BY
    dispatch_center,
    sku_code;

Show all active customer discount promotions for a specific entity, considering validity date and status flags.

SELECT
    def.policy_value
FROM core.policy_rel rel
JOIN core.policy_def def
    ON rel.entity_type = def.entity_type
   AND rel.policy_id = def.policy_id
WHERE rel.company_id = :company_id
  AND def.valid_from >= DATE ':start_date'
  AND rel.entity_type = :entity_type
  AND rel.is_active = :rel_flag
  AND def.is_active = :def_flag
GROUP BY def.policy_value,
         rel.company_id,
         rel.entity_type,
         rel.policy_id;