Problem Description

An order detail table records each order's ORDER_NUMBER, SKU, and WEBSITE. The task is to determine per order: if any line in the same order contains SKU 10000 or 20000, or if the website is not EU SITE, assign all lines of that order to WAREHOUSE 1; otherwise assign to WAREHOUSE 2. Add field FULFILLED_BY.

Source Data

Expected Result

Using a few representative orders as examples:

Orders 10001, 10002: contain special SKU 10000 or 20000, entire order assigned to WAREHOUSE 1.

Order 10003: all are EU SITE and no special SKU, entire order assigned to WAREHOUSE 2.

Orders 10004, 10005: WEBSITE is US SITE (non-EU), entire order assigned to WAREHOUSE 1. Order 10007: single-line order, non-special SKU, EU site, assigned to WAREHOUSE 2.

SQLazy Step-by-Step Implementation

Core idea: Group by order, use compute's max aggregation to check whether a special SKU exists in the order, then use the aggregation result and WEBSITE condition to calculate FULFILLED_BY for the entire order in one compute statement.

[Click to run this example online]

The steps are explained below.

Step 1: Compute warehouse assignment per order (core)

compute if (([10000,20000] contain SKU)then 1 else 0) max as order_has_special; if (order_has_special = 1 or WEBSITE <> "EU SITE" then "WAREHOUSE 1" else "WAREHOUSE 2") as FULFILLED_BY; partition ORDER_NUMBER

This is the core step. One compute statement calculates two fields: order_has_special: checks whether SKU is in the list [10000,20000] for each line within the order using contain for set membership, then aggregates with max to the order level - if any line matches, max returns 1. FULFILLED_BY: using the result above, assigns WAREHOUSE 1 when order_has_special=1 or WEBSITE is not EU SITE, otherwise WAREHOUSE 2. partition ORDER_NUMBER ensures aggregation and calculation happen per order.

Step 2: Select the final output columns

derive ORDER_NUMBER, SKU, WEBSITE, FULFILLED_BY

Use derive to keep the four required columns. Remove the auxiliary field order_has_special, and output the final result.

Generated SQL

After confirming the above steps, the SQLazy compiler automatically generates native SQL (Snowflake syntax):

WITH tb_detail_1 AS (
    SELECT ORDER_NUMBER, SKU, WEBSITE
        , MAX(CASE
            WHEN SKU IN (10000, 20000) THEN 1
            ELSE 0
        END) OVER (PARTITION BY ORDER_NUMBER) AS order_has_special
    FROM tb_detail
),
t1 AS (
    SELECT ORDER_NUMBER, SKU, WEBSITE, order_has_special
        , CASE
            WHEN order_has_special = 1
                OR WEBSITE <> 'EU SITE'
            THEN 'WAREHOUSE 1'
            ELSE 'WAREHOUSE 2'
        END AS FULFILLED_BY
    FROM tb_detail_1
)
SELECT ORDER_NUMBER, SKU, WEBSITE, FULFILLED_BY
FROM t1

SQLazy lets you describe logic in business language instead of writing nested SQL queries. In this example, a single compute statement handles both levels of logic: first aggregates an order-level flag, then assigns based on that flag - no need to write one CTE with MAX(CASE WHEN ... IN ...) OVER for window aggregation and another CTE for CASE WHEN as SQL requires. The contain function makes set membership checks as readable as everyday language. Step-by-step computation lets each intermediate result be independently verified.

Official Links

SQLazy Online Experience: sqlazy.com (free, no registration required)
SQLazy Repository: github.com/SPLWare/SQLazy