-- Keep the latest version of each order from a raw table
WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY order_id
           ORDER BY updated_at DESC
         ) AS rn
  FROM raw_orders
)
SELECT order_id, customer_id, amount, status, updated_at
FROM ranked
WHERE rn = 1;

-- Target table must have a unique key on order_id
INSERT INTO orders (order_id, customer_id, amount, status, updated_at)
SELECT order_id, customer_id, amount, status, updated_at
FROM staging_orders
ON CONFLICT (order_id) DO UPDATE
SET customer_id = EXCLUDED.customer_id,
    amount      = EXCLUDED.amount,
    status      = EXCLUDED.status,
    updated_at  = EXCLUDED.updated_at
WHERE orders.updated_at < EXCLUDED.updated_at;

-- 1. Close current rows whose tracked attribute changed
UPDATE dim_customer d
SET valid_to = CURRENT_DATE, is_current = FALSE
FROM staging_customers s
WHERE d.customer_id = s.customer_id
  AND d.is_current
  AND d.city <> s.city;

-- 2. Insert new current rows for changed or new customers
INSERT INTO dim_customer (customer_id, city, valid_from, valid_to, is_current)
SELECT s.customer_id, s.city, CURRENT_DATE, NULL, TRUE
FROM staging_customers s
LEFT JOIN dim_customer d
  ON d.customer_id = s.customer_id AND d.is_current
WHERE d.customer_id IS NULL;

-- Which city was the customer in when each order happened?
SELECT o.order_id, o.order_date, d.city
FROM fact_orders o
JOIN dim_customer d
  ON d.customer_id = o.customer_id
 AND o.order_date >= d.valid_from
 AND (d.valid_to IS NULL OR o.order_date < d.valid_to);

-- Each query should return 0 rows / 0 count when data is healthy
SELECT order_id, COUNT(*) FROM orders GROUP BY order_id HAVING COUNT(*) > 1;

SELECT COUNT(*) AS null_customers FROM orders WHERE customer_id IS NULL;

SELECT COUNT(*) AS bad_amounts FROM orders WHERE amount < 0;

SELECT COUNT(*) AS bad_status
FROM orders WHERE status NOT IN ('pending','paid','shipped','cancelled');

-- Compare today's load with the 7-day average
WITH daily AS (
  SELECT DATE(updated_at) AS d, COUNT(*) AS n
  FROM orders
  WHERE updated_at >= CURRENT_DATE - INTERVAL '8 days'
  GROUP BY DATE(updated_at)
)
SELECT (SELECT n FROM daily WHERE d = CURRENT_DATE) AS today,
       (SELECT AVG(n) FROM daily WHERE d < CURRENT_DATE) AS avg_7d;
-- alert if today is far below avg_7d

Data Engineer SQL Problem Set
1. Latest record per key (ROW_NUMBER)
2. Running total per customer (SUM OVER)
3. Gaps in daily data (date series + LEFT JOIN)
4. Upsert staging into target
5. SCD Type 2 load
6. Sessionise events (LAG + 30-minute rule)
7. Top N per group
8. Pivot rows to columns with CASE
9. Find duplicate keys and their counts
10. Month-over-month growth (LAG)

SQL Practice Log
Date      Problem              Solved alone?   Time   Edge cases noted
______    ______________       Y / N           ____   ______________
______    ______________       Y / N           ____   ______________
Redo any 'N' after three days without looking.

Interview Tracker (SQL rounds)
Company    Question                     Pattern           Outcome
_______    ______________________       dedup/window/...  ______
_______    ______________________       ______________    ______
Patterns I struggled with: ______________