SQL for Data Engineers — Beyond the Basics

SELECT
    order_id, store_id, order_amount,
    SUM(order_amount) OVER (
        PARTITION BY store_id          -- divide rows into groups by store
        ORDER BY order_date            -- within each partition, sort by date
        ROWS BETWEEN
            UNBOUNDED PRECEDING        -- from the first row of the partition
            AND CURRENT ROW            -- to the current row
    ) AS running_total_by_store
FROM silver.orders;

-- PARTITION BY is optional — omit it to treat all rows as one partition:
SUM(order_amount) OVER (ORDER BY order_date) AS running_total_all_stores

-- ORDER BY inside OVER is optional — omit it for unordered aggregations:
SUM(order_amount) OVER (PARTITION BY store_id) AS store_total
-- gives every row the store's total — same as a correlated subquery but much faster

-- Frame clause defaults: with ORDER BY → RANGE UNBOUNDED PRECEDING TO CURRENT ROW
--                         without ORDER BY → ROWS UNBOUNDED PRECEDING TO UNBOUNDED FOLLOWING

SELECT order_id, store_id, order_amount,

    -- ROW_NUMBER: sequential unique number within partition, no ties
    ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY order_amount DESC) AS row_num,

    -- RANK: tied rows get the same rank, then skips numbers
    -- Scores: 100, 100, 80 → ranks: 1, 1, 3
    RANK() OVER (PARTITION BY store_id ORDER BY order_amount DESC) AS rank_with_gaps,

    -- DENSE_RANK: tied rows get the same rank, no skipping
    -- Scores: 100, 100, 80 → ranks: 1, 1, 2
    DENSE_RANK() OVER (PARTITION BY store_id ORDER BY order_amount DESC) AS rank_no_gaps

FROM silver.orders
WHERE order_date = '2026-03-17';

WITH ranked AS (
    SELECT order_id, store_id, order_amount,
        ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY order_amount DESC) AS rn
    FROM silver.orders
    WHERE order_date = '2026-03-17'
)
SELECT order_id, store_id, order_amount
FROM ranked
WHERE rn <= 3;   -- keep only top 3 per store

-- WHY ROW_NUMBER FOR DEDUP vs RANK:
-- ROW_NUMBER guarantees one row per group even when values tie.
-- RANK may return 2 rows when two rows tie for position 1.
-- For deduplication, always use ROW_NUMBER.

-- LAG: access the value from a previous row in the partition
-- LEAD: access the value from a following row in the partition

SELECT store_id, order_date, daily_revenue,

    -- Yesterday's revenue for this store. Args: (column, offset, default_if_null)
    LAG(daily_revenue, 1, 0) OVER (PARTITION BY store_id ORDER BY order_date)
        AS prev_day_revenue,

    -- Day-over-day change
    daily_revenue - LAG(daily_revenue, 1, 0) OVER (PARTITION BY store_id ORDER BY order_date)
        AS day_over_day_change,

    -- Tomorrow's revenue
    LEAD(daily_revenue, 1) OVER (PARTITION BY store_id ORDER BY order_date)
        AS next_day_revenue

FROM gold.daily_store_revenue
WHERE order_date BETWEEN '2026-03-01' AND '2026-03-17'
ORDER BY store_id, order_date;

-- Careful with % change — LAG can be 0, so guard the division:
SELECT store_id, order_date, daily_revenue,
    CASE
        WHEN LAG(daily_revenue, 1) OVER (PARTITION BY store_id ORDER BY order_date) IS NULL
          OR LAG(daily_revenue, 1) OVER (PARTITION BY store_id ORDER BY order_date) = 0
        THEN NULL
        ELSE ROUND(
            (daily_revenue - LAG(daily_revenue, 1) OVER (PARTITION BY store_id ORDER BY order_date))
            / LAG(daily_revenue, 1) OVER (PARTITION BY store_id ORDER BY order_date) * 100, 2)
    END AS pct_change
FROM gold.daily_store_revenue;

-- Find dates where a store had no data at all (gap detection):
WITH store_dates AS (
    SELECT store_id, order_date,
        LEAD(order_date) OVER (PARTITION BY store_id ORDER BY order_date) AS next_date
    FROM gold.daily_store_revenue
)
SELECT store_id, order_date, next_date, next_date - order_date - 1 AS missing_days
FROM store_dates
WHERE next_date - order_date > 1   -- gap of more than 1 day
ORDER BY missing_days DESC;

SELECT store_id, order_date, daily_revenue,

    -- Running total (cumulative sum from start of partition to current row)
    SUM(daily_revenue) OVER (
        PARTITION BY store_id ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_revenue,

    -- 7-day moving average (last 7 days including today)
    AVG(daily_revenue) OVER (
        PARTITION BY store_id ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d,

    -- Month-to-date total (from first day of current month to today)
    SUM(daily_revenue) OVER (
        PARTITION BY store_id, DATE_TRUNC('month', order_date)
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS mtd_revenue,

    -- Percentage of store's total monthly revenue
    daily_revenue / SUM(daily_revenue) OVER (
        PARTITION BY store_id, DATE_TRUNC('month', order_date)
    ) * 100 AS pct_of_monthly_total

FROM gold.daily_store_revenue
ORDER BY store_id, order_date;

ROWS vs RANGE — an important distinction:

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  → exactly 6 rows before current row, regardless of value gaps
  → correct for "last 7 rows"

RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
  → all rows within the last 6 calendar days by value
  → correct for "last 7 calendar days" even if some days have no data
  → only works when ORDER BY column is DATE or TIMESTAMP

For moving averages over time series with gaps, RANGE is more correct.
For moving averages over sequences of rows, ROWS is more correct.

SELECT customer_id, total_spend_ytd,

    -- NTILE(n): divide rows into n equal-sized buckets (quartile/decile segmentation)
    NTILE(4) OVER (ORDER BY total_spend_ytd DESC) AS spend_quartile,
    -- 1 = top 25%, 2 = next 25%, 3 = next 25%, 4 = bottom 25%

    -- PERCENT_RANK: what percentile is this row in? (0.0 to 1.0)
    ROUND(PERCENT_RANK() OVER (ORDER BY total_spend_ytd) * 100, 1) AS percentile_rank,

    -- CUME_DIST: cumulative distribution (fraction of rows <= current)
    ROUND(CUME_DIST() OVER (ORDER BY total_spend_ytd) * 100, 1) AS cumulative_pct

FROM silver.customer_annual_stats
ORDER BY total_spend_ytd DESC;

-- REAL USE CASE: customer segmentation for marketing
SELECT customer_id, total_orders, total_spend_ytd,
    CASE NTILE(5) OVER (ORDER BY total_spend_ytd DESC)
        WHEN 1 THEN 'Platinum' WHEN 2 THEN 'Gold' WHEN 3 THEN 'Silver'
        WHEN 4 THEN 'Bronze'   WHEN 5 THEN 'Standard'
    END AS customer_tier
FROM silver.customer_annual_stats;

SELECT s.store_name, ranked.daily_revenue, ranked.revenue_rank
FROM (
    SELECT store_id, SUM(order_amount) AS daily_revenue,
        RANK() OVER (ORDER BY SUM(order_amount) DESC) AS revenue_rank
    FROM (
        SELECT order_id, store_id, order_amount
        FROM silver.orders
        WHERE order_date = '2026-03-17' AND status = 'delivered'
    ) filtered
    GROUP BY store_id
) ranked
JOIN silver.stores s ON ranked.store_id = s.store_id
WHERE ranked.revenue_rank <= 5;

WITH
-- Step 1: filter to the orders we care about
delivered_orders AS (
    SELECT order_id, store_id, order_amount
    FROM silver.orders
    WHERE order_date = '2026-03-17' AND status = 'delivered'
),
-- Step 2: aggregate by store
store_revenue AS (
    SELECT store_id, SUM(order_amount) AS daily_revenue, COUNT(*) AS order_count
    FROM delivered_orders
    GROUP BY store_id
),
-- Step 3: rank stores by revenue
ranked_stores AS (
    SELECT store_id, daily_revenue, order_count,
        RANK() OVER (ORDER BY daily_revenue DESC) AS revenue_rank
    FROM store_revenue
)
-- Final: join to store names and filter top 5
SELECT s.store_name, s.city, rs.daily_revenue, rs.order_count, rs.revenue_rank
FROM ranked_stores rs
JOIN silver.stores s ON rs.store_id = s.store_id
WHERE rs.revenue_rank <= 5
ORDER BY rs.revenue_rank;

WITH
-- Base: all orders in the analysis window
base_orders AS (
    SELECT o.order_id, o.customer_id, o.store_id, o.order_amount, o.status,
           o.order_date, o.created_at
    FROM silver.orders o
    WHERE o.order_date BETWEEN '2026-01-01' AND '2026-03-17'
      AND o.status IN ('delivered', 'cancelled')
),
-- Customer first order date (for cohort analysis)
customer_first_order AS (
    SELECT customer_id, MIN(order_date) AS first_order_date, MIN(order_id) AS first_order_id
    FROM base_orders
    WHERE status = 'delivered'
    GROUP BY customer_id
),
-- Delivered orders only (for revenue metrics)
delivered AS (
    SELECT * FROM base_orders WHERE status = 'delivered'
),

customer_metrics AS (
    SELECT
        d.customer_id,
        COUNT(DISTINCT d.order_id) AS total_orders,
        SUM(d.order_amount)        AS total_revenue,
        AVG(d.order_amount)        AS avg_order_value,
        MAX(d.order_date)          AS last_order_date,
        COUNT(DISTINCT d.store_id) AS stores_visited,
        cfo.first_order_date,
        ('2026-03-17'::DATE - cfo.first_order_date) AS customer_age_days,
        -- Order frequency (orders per month since first order):
        ROUND(
            COUNT(DISTINCT d.order_id)::NUMERIC
            / GREATEST(('2026-03-17'::DATE - cfo.first_order_date) / 30.0, 1), 2
        ) AS orders_per_month
    FROM delivered d
    JOIN customer_first_order cfo USING (customer_id)
    GROUP BY d.customer_id, cfo.first_order_date
),

final AS (
    SELECT
        cm.*, c.customer_name, c.city, c.tier,
        CASE
            WHEN cm.total_revenue >= 50000 THEN 'high_value'
            WHEN cm.total_revenue >= 10000 THEN 'mid_value'
            ELSE 'low_value'
        END AS value_segment,
        CASE
            WHEN '2026-03-17'::DATE - cm.last_order_date <= 30 THEN 'active'
            WHEN '2026-03-17'::DATE - cm.last_order_date <= 90 THEN 'at_risk'
            ELSE 'churned'
        END AS recency_segment
    FROM customer_metrics cm
    JOIN silver.customers c USING (customer_id)
)
SELECT * FROM final;

-- categories table: (category_id, name, parent_category_id)
-- Root categories have parent_category_id = NULL

WITH RECURSIVE category_tree AS (
    -- Base case: start with root categories (no parent)
    SELECT category_id, name, parent_category_id,
           name AS full_path, 0 AS depth
    FROM silver.categories
    WHERE parent_category_id IS NULL

    UNION ALL

    -- Recursive case: join each category to its children
    SELECT c.category_id, c.name, c.parent_category_id,
           ct.full_path || ' > ' || c.name AS full_path,
           ct.depth + 1 AS depth
    FROM silver.categories c
    JOIN category_tree ct ON c.parent_category_id = ct.category_id
)
SELECT category_id, name, full_path, depth
FROM category_tree
ORDER BY full_path;

Electronics                              depth=0
Electronics > Phones                     depth=1
Electronics > Phones > Smartphones       depth=2
Electronics > Phones > Feature Phones    depth=2
Electronics > Laptops                    depth=1

GUARD AGAINST INFINITE LOOPS: add WHERE depth < 10 to the recursive case.
Some databases support a MAXRECURSION hint as an additional safeguard.

SELECT DISTINCT * FROM silver.orders;
-- Simple but scans all rows and you cannot control which row is kept

-- Better with a CTE — explicit control over which copy survives:
WITH deduped AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY order_id, customer_id, amount, status) AS rn
    FROM silver.orders
)
SELECT * EXCLUDE (rn) FROM deduped WHERE rn = 1;

WITH deduped AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY order_id          -- group by business key
            ORDER BY updated_at DESC       -- most recent first
        ) AS rn
    FROM silver.orders
)
SELECT * EXCLUDE (rn) FROM deduped WHERE rn = 1;
-- For each order_id, keeps exactly one row — the one with the latest updated_at

WITH deduped AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY order_id
            ORDER BY created_at ASC,    -- oldest first
                     ingested_at ASC    -- tiebreak by when the pipeline saw it
        ) AS rn
    FROM silver.orders
)
SELECT * EXCLUDE (rn) FROM deduped WHERE rn = 1;

-- Snowflake supports QUALIFY to filter on window function results directly:
SELECT *
FROM silver.orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) = 1;
-- Cleaner than wrapping in a CTE — equivalent result

-- Step 1: identify rows to keep
CREATE TEMP TABLE orders_deduped AS
SELECT * EXCLUDE (rn)
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) AS rn
    FROM silver.orders
) t
WHERE rn = 1;

-- Step 2: swap (truncate original, insert clean version)
BEGIN;
TRUNCATE silver.orders;
INSERT INTO silver.orders SELECT * FROM orders_deduped;
COMMIT;

-- Or use DELETE with a CTE (PostgreSQL / Snowflake):
WITH duplicates AS (
    SELECT ctid, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) AS rn
    FROM silver.orders
)
DELETE FROM silver.orders
WHERE ctid IN (SELECT ctid FROM duplicates WHERE rn > 1);

-- NULL arithmetic — results are always NULL
SELECT 380 + NULL;          -- NULL
SELECT NULL * 0;            -- NULL (not 0!)
SELECT 'hello' || NULL;     -- NULL (not 'hello')

-- NULL comparisons — never use = NULL
SELECT * FROM orders WHERE promo_code = NULL;   -- WRONG: returns 0 rows
SELECT * FROM orders WHERE promo_code IS NULL;  -- CORRECT

-- NULL in three-valued logic:
-- TRUE  AND NULL = NULL (not FALSE)     FALSE AND NULL = FALSE
-- TRUE  OR  NULL = TRUE                 FALSE OR  NULL = NULL (not FALSE)
-- NOT NULL       = NULL

-- Consequence: WHERE clause excludes rows where the condition is NULL.
-- If promo_code is NULL, these rows are silently excluded:
SELECT * FROM orders WHERE promo_code != 'SAVE10';
-- Rows where promo_code IS NULL are NOT returned — they fail the != comparison

-- FIX:
SELECT * FROM orders WHERE promo_code != 'SAVE10' OR promo_code IS NULL;

SELECT
    COUNT(*)              AS total_rows,         -- counts ALL rows including NULL
    COUNT(promo_code)     AS rows_with_promo,    -- counts only non-NULL promo_code
    COUNT(*) - COUNT(promo_code) AS rows_without_promo,
    AVG(order_amount)     AS avg_amount,         -- NULL amounts excluded from avg
    SUM(discount_amount)  AS total_discount      -- NULL discounts IGNORED, not treated as 0
FROM silver.orders;

-- Safe pattern: use COALESCE to treat NULL as 0 in sums
SELECT SUM(COALESCE(discount_amount, 0)) AS total_discount FROM silver.orders;

-- COALESCE: return the first non-NULL value
SELECT order_id,
    COALESCE(promo_code, 'NO_PROMO')  AS promo_code_safe,
    COALESCE(delivery_fee, 0)          AS delivery_fee_safe,
    COALESCE(notes, special_inst, '') AS display_notes  -- try notes, then special_inst, then ''
FROM silver.orders;

-- NULLIF: return NULL if the value equals a specific value — prevents /0 elegantly
SELECT store_id, total_revenue / NULLIF(total_orders, 0) AS avg_order_value
FROM gold.store_metrics;
-- if total_orders = 0, NULLIF returns NULL → NULL/anything = NULL (not a division error)

-- Clean up placeholder values:
SELECT NULLIF(phone_number, 'N/A') AS phone_clean FROM customers;

-- NULL = NULL is UNKNOWN, so NULL join keys never match
SELECT o.*, c.name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;
-- If o.customer_id IS NULL → no match → c.name IS NULL in the result

-- Common mistake: assuming every LEFT JOIN null comes from a missing match.
-- Some come from a NULL join key in the left table itself — distinguish them:
SELECT o.order_id, o.customer_id, c.name,
    CASE
        WHEN o.customer_id IS NULL THEN 'null_key_in_orders'
        WHEN c.customer_id IS NULL THEN 'missing_customer'
        ELSE 'matched'
    END AS join_status
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;

-- UNION ALL: include all rows from both queries (keeps duplicates)
-- Use UNION ALL by default — faster, no deduplication step
SELECT order_id, order_amount, 'online' AS channel FROM silver.online_orders
UNION ALL
SELECT order_id, order_amount, 'instore' AS channel FROM silver.instore_orders;

-- UNION (without ALL): deduplicate — removes rows that appear in both results
-- Slower — requires a sort or hash to find duplicates
SELECT customer_id FROM silver.email_subscribers
UNION
SELECT customer_id FROM silver.sms_subscribers;
-- Returns unique customer_ids who subscribed to either channel

-- INTERSECT: rows that appear in BOTH queries
SELECT customer_id FROM silver.email_subscribers
INTERSECT
SELECT customer_id FROM silver.sms_subscribers;

-- Equivalent with JOIN (sometimes more explicit):
SELECT DISTINCT e.customer_id
FROM silver.email_subscribers e
INNER JOIN silver.sms_subscribers s USING (customer_id);

-- EXCEPT: rows in the first query but NOT in the second (MINUS in Oracle)
SELECT customer_id FROM silver.email_subscribers
EXCEPT
SELECT customer_id FROM silver.sms_subscribers;

-- Find order_ids in the source (PostgreSQL) but missing from the warehouse
SELECT order_id FROM source.orders WHERE order_date = '2026-03-17'
EXCEPT
SELECT order_id FROM silver.orders WHERE order_date = '2026-03-17';
-- Returns order_ids that were ingested but not yet in the warehouse.
-- If this returns rows: the pipeline has a gap.

WITH all_payments AS (
    SELECT payment_id, merchant_id, amount, 'stripe' AS provider, created_at
    FROM silver.stripe_payments
    UNION ALL
    SELECT payment_id, merchant_id, amount, 'paypal' AS provider, created_at
    FROM silver.paypal_payments
    UNION ALL
    SELECT payment_id, merchant_id, amount, 'square' AS provider, created_at
    FROM silver.square_payments
)
SELECT provider, DATE_TRUNC('day', created_at) AS payment_date,
    COUNT(*) AS transaction_count, SUM(amount) AS total_volume
FROM all_payments
GROUP BY 1, 2
ORDER BY 2, 1;

-- Use when: you only care about current state, history is not needed
INSERT INTO silver.customers (customer_id, name, city, updated_at)
VALUES (4201938, 'Emily Johnson', 'Austin', NOW())
ON CONFLICT (customer_id)
DO UPDATE SET city = EXCLUDED.city, updated_at = EXCLUDED.updated_at;

-- PROBLEM: all historical analysis now shows Austin.
-- "How much did Emily spend while she lived in Seattle?" → impossible to answer.
-- Use SCD Type 2 if that question matters to the business.

CREATE TABLE silver.customers_scd2 (
    customer_sk    BIGSERIAL PRIMARY KEY,   -- surrogate key (new per version)
    customer_id    BIGINT    NOT NULL,      -- business key (same across versions)
    name           VARCHAR   NOT NULL,
    city           VARCHAR   NOT NULL,
    tier           VARCHAR   NOT NULL,
    valid_from     DATE      NOT NULL,      -- when this version became active
    valid_to       DATE,                    -- when this version expired (NULL = current)
    is_current     BOOLEAN   NOT NULL DEFAULT TRUE,
    UNIQUE (customer_id, valid_from)
);

-- STEP 1: expire the current row
UPDATE silver.customers_scd2
SET valid_to = CURRENT_DATE - INTERVAL '1 day', is_current = FALSE
WHERE customer_id = 4201938 AND is_current = TRUE;

-- STEP 2: insert the new version
INSERT INTO silver.customers_scd2
    (customer_id, name, city, tier, valid_from, valid_to, is_current)
VALUES
    (4201938, 'Emily Johnson', 'Austin', 'Gold', CURRENT_DATE, NULL, TRUE);

-- QUERY: "What city was Emily in when she placed order 9284751?"
SELECT c.city
FROM silver.orders o
JOIN silver.customers_scd2 c
  ON o.customer_id = c.customer_id
 AND o.order_date BETWEEN c.valid_from AND COALESCE(c.valid_to, '9999-12-31')
WHERE o.order_id = 9284751;
-- Returns 'Seattle' if placed before the move, 'Austin' if placed after

-- dbt snapshot pattern (generates SCD2 automatically):
-- {% snapshot customers_snapshot %}
-- {{ config(target_schema='snapshots', unique_key='customer_id',
--           strategy='check', check_cols=['city', 'tier']) }}
-- SELECT customer_id, name, city, tier FROM {{ source('prod', 'customers') }}
-- {% endsnapshot %}

-- Use when: you only need to track one change back (current + previous)
CREATE TABLE silver.customers_scd3 (
    customer_id      BIGINT  PRIMARY KEY,
    name             VARCHAR NOT NULL,
    current_tier     VARCHAR NOT NULL,
    previous_tier    VARCHAR,          -- NULL if never changed
    tier_changed_at  TIMESTAMPTZ,
    city             VARCHAR NOT NULL
);

UPDATE silver.customers_scd3
SET previous_tier = current_tier,   -- save current as previous
    current_tier   = 'Platinum',    -- set new current
    tier_changed_at = NOW()
WHERE customer_id = 4201938;

-- LIMITATION: only tracks one previous value. After a second change,
-- previous_tier is overwritten — cannot answer "two changes ago?"
-- Use SCD Type 2 for full history.

SELECT order_id, created_at,
    DATE_TRUNC('day',   created_at) AS order_day,        -- 2026-03-17 00:00:00
    DATE_TRUNC('week',  created_at) AS order_week_start, -- 2026-03-16 (Monday)
    DATE_TRUNC('month', created_at) AS order_month,      -- 2026-03-01 00:00:00
    DATE_TRUNC('year',  created_at) AS order_year        -- 2026-01-01 00:00:00
FROM silver.orders;
-- Snowflake: DATE_TRUNC('month', created_at) — same syntax
-- BigQuery:  DATE_TRUNC(created_at, MONTH)   — arguments reversed!

SELECT
    CURRENT_DATE - INTERVAL '7 days'  AS week_ago,
    CURRENT_DATE + INTERVAL '30 days' AS thirty_ahead,
    EXTRACT(DOW FROM CURRENT_DATE)    AS day_of_week,   -- 0=Sunday, 6=Saturday
    EXTRACT(EPOCH FROM created_at)    AS unix_ts,       -- seconds since 1970
    CURRENT_DATE - order_date         AS days_since_order,
    AGE(CURRENT_DATE, order_date)     AS age_interval   -- interval '14 days'
FROM silver.orders;

-- Last 7 days (inclusive of today):
WHERE order_date >= CURRENT_DATE - INTERVAL '6 days'

-- Month-to-date:
WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE)
  AND order_date <  CURRENT_DATE + INTERVAL '1 day'

-- Previous full month:
WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
  AND order_date <  DATE_TRUNC('month', CURRENT_DATE)

-- Last complete week (Monday–Sunday):
WHERE order_date >= DATE_TRUNC('week', CURRENT_DATE - INTERVAL '7 days')
  AND order_date <  DATE_TRUNC('week', CURRENT_DATE)

-- Always work in UTC internally, convert to local time only for display

-- PostgreSQL:
SELECT order_id,
    created_at AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York' AS created_at_local
FROM silver.orders;

-- Snowflake:  SELECT CONVERT_TIMEZONE('UTC', 'America/New_York', created_at) ...
-- BigQuery:   SELECT DATETIME(created_at, 'America/New_York') ...

-- Safe local-day grouping (orders placed between midnight and midnight local time):
SELECT DATE(created_at AT TIME ZONE 'America/New_York') AS order_date_local,
    COUNT(*) AS order_count
FROM silver.orders
GROUP BY 1 ORDER BY 1;

-- Monthly cohort retention: for each signup month cohort,
-- what % of customers are still ordering in month N?

WITH
-- Step 1: assign each customer to their signup cohort (month of first order)
cohort_assignment AS (
    SELECT customer_id, DATE_TRUNC('month', MIN(order_date)) AS cohort_month
    FROM silver.orders
    WHERE status = 'delivered'
    GROUP BY customer_id
),
-- Step 2: for each customer, list every month they had at least one order
active_months AS (
    SELECT DISTINCT customer_id, DATE_TRUNC('month', order_date) AS active_month
    FROM silver.orders
    WHERE status = 'delivered'
),

-- Step 3: join and calculate cohort_period (0 = cohort month, 1 = one month later)
cohort_data AS (
    SELECT ca.cohort_month, am.active_month,
        EXTRACT(YEAR FROM AGE(am.active_month, ca.cohort_month)) * 12
        + EXTRACT(MONTH FROM AGE(am.active_month, ca.cohort_month)) AS cohort_period,
        COUNT(DISTINCT am.customer_id) AS customers_active
    FROM cohort_assignment ca
    JOIN active_months am USING (customer_id)
    WHERE am.active_month >= ca.cohort_month
    GROUP BY 1, 2, 3
),
-- Step 4: calculate cohort size (customers in month 0)
cohort_sizes AS (
    SELECT cohort_month, customers_active AS cohort_size
    FROM cohort_data WHERE cohort_period = 0
)
-- Final: retention rates
SELECT cd.cohort_month, cs.cohort_size, cd.cohort_period, cd.customers_active,
    ROUND(cd.customers_active::NUMERIC / cs.cohort_size * 100, 1) AS retention_pct
FROM cohort_data cd
JOIN cohort_sizes cs USING (cohort_month)
ORDER BY cohort_month, cohort_period;

EXPLAIN ANALYZE
SELECT s.store_name, COUNT(*) AS order_count, SUM(o.amount) AS total_revenue
FROM silver.orders o
JOIN silver.stores s ON o.store_id = s.store_id
WHERE o.order_date >= '2026-01-01'
GROUP BY s.store_name;

-- Sample output:
-- HashAggregate (actual time=892ms rows=10)
--   ->  Hash Join (actual time=24ms rows=482k)
--         ->  Index Scan using idx_orders_date on orders o (rows=482193)
--         ->  Hash -> Seq Scan on stores s (rows=10)
-- Execution Time: 896 ms

KEY THINGS TO LOOK FOR:
"Seq Scan" on a large table       → likely missing index
"Index Scan"                      → index is being used ✓
"Hash Join"                       → joining medium tables efficiently ✓
"Nested Loop" on large tables      → may be slow
rows estimate vs actual differing by 10×+  → stale statistics
high "actual time" on one node     → that node is the bottleneck

EXPLAIN
SELECT store_name, SUM(amount) FROM orders WHERE order_date >= '2026-01-01'
GROUP BY store_name;

-- Look for in Snowflake:
--   "Partition pruning: 847 of 1024 partitions pruned" → filter is working ✓
--   "TableScan: ALL PARTITIONS" → no pruning → check clustering key
--   "SpillToLocalStorage" → query is spilling — increase warehouse size or
--                           rewrite the query to reduce intermediate result size

-- 1. FILTER EARLY — push WHERE conditions as early as possible in CTEs
-- GOOD: filter before the JOIN, reducing rows joining
WITH recent_orders AS (SELECT * FROM orders WHERE order_date >= '2026-03-01')
SELECT * FROM recent_orders o JOIN customers c USING (customer_id);

-- 2. AVOID SELECT * — on columnar warehouses it reads every column,
-- negating the columnar benefit
SELECT order_id, customer_id, amount FROM orders;

-- 3. AVOID FUNCTIONS ON INDEXED/FILTERED COLUMNS — prevents index/pruning use
-- BAD:  WHERE EXTRACT(YEAR FROM order_date) = 2026    (full scan!)
-- GOOD: WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01'

-- 4. USE JOINS INSTEAD OF CORRELATED SUBQUERIES — a correlated subquery
-- runs once per outer row; a JOIN runs once.

-- 5. COUNT(*) is the SQL standard — no practical difference from COUNT(1)
-- in modern databases. Use COUNT(*).

-- 6. AVOID DISTINCT WHEN NOT NEEDED — it triggers an extra sort/hash step.
-- If the join is known to produce unique rows, omit DISTINCT.

-- 7. MATERIALISE INTERMEDIATE CTEs for reuse in Snowflake / BigQuery —
-- a CTE referenced multiple times may be re-executed each time
-- (database-dependent). Use CREATE TEMP TABLE for CTEs referenced >once
-- in complex queries.

-- 8. PARTITION PRUNING — always filter on partition columns. Without a
-- partition filter: full table scan regardless of other filters.

-- 9. JOIN ORDER — put the smaller table on the right in HASH JOINs.
-- Most optimisers (PostgreSQL) handle this automatically; for very large
-- tables in Snowflake/BigQuery, hint with the smaller table on the right.

-- 10. APPROXIMATE FUNCTIONS for exploration on very large datasets:
SELECT APPROX_COUNT_DISTINCT(customer_id) FROM orders;             -- Snowflake
SELECT HLL_COUNT.MERGE(HLL_COUNT.INIT(customer_id)) FROM orders;    -- BigQuery
-- Typically within 1–2% of the exact count, but runs much faster.

-- PostgreSQL:
SELECT payload->>'order_id' AS order_id, (payload->>'amount')::DECIMAL
FROM orders WHERE payload IS NOT NULL;

-- Snowflake (VARIANT column):
SELECT payload:order_id::INTEGER          AS order_id,
       payload:customer.city::VARCHAR     AS city,        -- nested path
       payload:items[0]:name::VARCHAR     AS first_item   -- array index
FROM orders;

-- BigQuery (JSON column):
SELECT JSON_VALUE(payload, '$.order_id') AS order_id,
       JSON_VALUE(payload, '$.customer.city') AS city
FROM orders;

-- ARRAY UNPACKING:
SELECT order_id, UNNEST(items) AS item FROM orders;                    -- PostgreSQL
SELECT o.order_id, f.value:item_name::VARCHAR FROM orders o,
       LATERAL FLATTEN(input => o.items) f;                            -- Snowflake
SELECT order_id, item FROM orders, UNNEST(items) AS item;              -- BigQuery

-- PostgreSQL requires a CTE to filter on a window function:
WITH ranked AS (SELECT *, ROW_NUMBER() OVER (...) AS rn FROM orders)
SELECT * FROM ranked WHERE rn = 1;

-- Snowflake / BigQuery — QUALIFY filters window results directly:
SELECT * FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) = 1;

-- String splitting:
-- PostgreSQL/Snowflake: SPLIT_PART(str, delimiter, field_number)
-- BigQuery:             SPLIT(str, delimiter)[OFFSET(0)]  — returns an array

-- Regex:
-- PostgreSQL: ~ for match, regexp_replace(), regexp_extract()
-- Snowflake:  REGEXP_LIKE(), REGEXP_REPLACE(), REGEXP_SUBSTR()
-- BigQuery:   REGEXP_CONTAINS(), REGEXP_REPLACE(), REGEXP_EXTRACT()

-- Date truncation — BigQuery reverses the argument order:
DATE_TRUNC('month', ts)         -- PostgreSQL, Snowflake
DATE_TRUNC(ts, MONTH)           -- BigQuery — reversed!

-- Adding intervals:
ts + INTERVAL '7 days'          -- PostgreSQL
DATEADD('day', 7, ts)           -- Snowflake
DATE_ADD(ts, INTERVAL 7 DAY)    -- BigQuery

-- Limiting rows:
SELECT * FROM orders LIMIT 10;                    -- PostgreSQL, Snowflake, BigQuery
SELECT TOP 10 * FROM orders;                       -- SQL Server / Redshift (also supports LIMIT)
SELECT * FROM orders FETCH FIRST 10 ROWS ONLY;     -- SQL standard

The analytics team needs a Gold layer table that powers the daily revenue dashboard. Requirements: daily revenue per store per category, with running month-to-date totals, day-over-day change, and store ranking within category. This is a complete production dbt model using every pattern from this module.

Base and daily aggregation

-- Refresh: daily at 06:00 AM ET

WITH
-- Step 1: Base — delivered orders in the analysis window,
-- filter pushed down to Silver before any aggregation
base AS (
    SELECT o.order_id, o.store_id, p.category, o.order_amount,
        DATE(o.created_at AT TIME ZONE 'America/New_York') AS order_date
    FROM silver.orders o
    JOIN silver.order_items oi ON o.order_id = oi.order_id
    JOIN silver.products p    ON oi.product_id = p.product_id
    WHERE o.status = 'delivered'
      AND o.created_at >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '2 months')
    -- 2 months back to support MoM comparisons
),
-- Step 2: Daily aggregation by store + category
daily_agg AS (
    SELECT store_id, category, order_date,
        COUNT(DISTINCT order_id) AS order_count,
        SUM(order_amount)        AS daily_revenue
    FROM base
    GROUP BY 1, 2, 3
),

The window function layer

-- Step 3: Window function layer — running totals, comparisons, rankings
enriched AS (
    SELECT store_id, category, order_date, order_count, daily_revenue,

        -- Running month-to-date revenue
        SUM(daily_revenue) OVER (
            PARTITION BY store_id, category, DATE_TRUNC('month', order_date)
            ORDER BY order_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS mtd_revenue,

        -- Day-over-day revenue change
        daily_revenue - COALESCE(LAG(daily_revenue) OVER (
            PARTITION BY store_id, category ORDER BY order_date
        ), 0) AS dod_revenue_change,

        -- 7-day moving average
        ROUND(AVG(daily_revenue) OVER (
            PARTITION BY store_id, category ORDER BY order_date
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ), 2) AS moving_avg_7d,

        -- Rank within category on each day (which store performed best?)
        RANK() OVER (
            PARTITION BY category, order_date ORDER BY daily_revenue DESC
        ) AS store_rank_in_category

    FROM daily_agg
),

Final dimension join

final AS (
    SELECT e.*, s.store_name, s.city, s.store_manager
    FROM enriched e
    JOIN silver.stores s USING (store_id)
)
SELECT * FROM final
ORDER BY order_date DESC, category, store_rank_in_category;

This model uses every major pattern from this module: CTE chain for readability and testability, filter pushdown in the base CTE, window functions for running totals and rankings, LAG with COALESCE for safe day-over-day comparison, and a clean final JOIN to dimension attributes. A data engineer who can write this kind of SQL confidently is ready for a production data engineering role.