First SQL shift

Do this after the window and habits lessons (through lesson 15).

You are the analyst on call for a tiny retail warehouse. Postgres is local; the website runs nothing. Work in ~/dcubes/sql-lab/shift (or reuse ~/dcubes/sql-lab if still seeded).

What you need

• Seeded DB from SQL Getting Started (5 stores, 12 products, 300 orders)
• Docker running; psql on your PATH

1. Confirm the lab

mkdir -p ~/dcubes/sql-lab/out
cd ~/dcubes/sql-lab
export PGPASSWORD=dcubes PGHOST=127.0.0.1 PGPORT=15432 PGUSER=dcubes PGDATABASE=retail
psql -c "SELECT count(*) FROM orders;"

 count
-------
   300

If empty, re-run Getting Started load steps.

2. Revenue by category (completed only)

psql -c "
SELECT p.category,
       count(*) AS orders,
       round(sum(o.line_total), 2) AS revenue
FROM orders o
JOIN products p ON o.sku = p.sku
WHERE o.status = 'completed'
GROUP BY p.category
ORDER BY revenue DESC;
" | tee out/q1_category_revenue.txt

You should see four categories with revenue ordered high to low.

3. Top stores by completed order count

psql -c "
SELECT s.store_id, s.store_name, s.region, count(*) AS completed_orders
FROM orders o
JOIN stores s ON o.store_id = s.store_id
WHERE o.status = 'completed'
GROUP BY s.store_id, s.store_name, s.region
ORDER BY completed_orders DESC, s.store_id;
" | tee out/q2_stores.txt

4. Repeat customers (2+ orders any status)

psql -c "
SELECT customer_id, count(*) AS orders
FROM orders
GROUP BY customer_id
HAVING count(*) >= 2
ORDER BY orders DESC, customer_id
LIMIT 15;
" | tee out/q3_repeat_customers.txt

5. CTE pipeline — completed lines with region, then bucket

psql -c "
WITH completed AS (
  SELECT o.*, s.region
  FROM orders o
  JOIN stores s ON o.store_id = s.store_id
  WHERE o.status = 'completed'
),
bucketed AS (
  SELECT region,
         CASE
           WHEN line_total < 5 THEN 'small'
           WHEN line_total < 15 THEN 'medium'
           ELSE 'large'
         END AS size_bucket,
         line_total
  FROM completed
)
SELECT region, size_bucket, count(*) AS n, round(sum(line_total), 2) AS revenue
FROM bucketed
GROUP BY region, size_bucket
ORDER BY region, size_bucket;
" | tee out/q4_region_buckets.txt

6. Window — top 3 completed lines per store

psql -c "
WITH ranked AS (
  SELECT
    o.store_id,
    o.order_id,
    o.line_total,
    row_number() OVER (PARTITION BY o.store_id ORDER BY o.line_total DESC) AS rn
  FROM orders o
  WHERE o.status = 'completed'
)
SELECT store_id, order_id, line_total, rn
FROM ranked
WHERE rn <= 3
ORDER BY store_id, rn;
" | tee out/q5_top3_per_store.txt

7. Day-over-day with LAG

psql -c "
WITH daily AS (
  SELECT order_ts::date AS d, round(sum(line_total), 2) AS revenue
  FROM orders
  WHERE status = 'completed'
  GROUP BY 1
)
SELECT d, revenue,
       lag(revenue) OVER (ORDER BY d) AS prev_revenue,
       round(revenue - lag(revenue) OVER (ORDER BY d), 2) AS change
FROM daily
ORDER BY d DESC
LIMIT 10;
" | tee out/q6_dod.txt

8. Export CSV for one answer

psql -c "\copy (
  SELECT p.category, round(sum(o.line_total), 2) AS revenue
  FROM orders o
  JOIN products p ON o.sku = p.sku
  WHERE o.status = 'completed'
  GROUP BY p.category
  ORDER BY revenue DESC
) TO 'out/category_revenue.csv' CSV HEADER"
cat out/category_revenue.csv

You passed if

• orders count is 300
• Six out/q*.txt files exist with sensible tables
• out/category_revenue.csv has a header and four category rows
• Top-3-per-store query returns at most 3 rows per store_id

Common mistakes

• Forgetting status = 'completed' on revenue questions
• Filtering window rn without a CTE
• Writing exports to a path Postgres server cannot see — \copy to a path under your lab is correct

Next

Next on the roadmap: Git — version the SQL you just wrote. Still guidance only, still on your machine.