Advanced SQL in 14 days

The 14-day plan

1. D1-2

   CASE WHEN

   • CASE WHEN inside SELECT
   • Turn rows into columns
   • SUM(CASE WHEN ... THEN 1 ELSE 0 END)
2. D3-4

   Subqueries and CTEs

   • Subquery in WHERE and FROM
   • WITH ... AS, chain 2-3 CTEs
   • EXISTS vs IN
3. D5-8

   Ranking window functions

   • ROW_NUMBER, RANK, DENSE_RANK
   • PARTITION BY to rank inside groups
   • Top N per group
4. D9-11

   Running totals

   • Running total with SUM() OVER
   • 3-month moving average
   • LAG / LEAD for month-over-month
5. D12-14

   Dates + mocks

   • Month buckets, first purchase month
   • COALESCE and NULLs in counts
   • 20 timed problems, out loud

The 2 queries to master

1. 01

   Top 3 per category

   DENSE_RANK keeps ties together.

   WITH r AS (
     SELECT category, product, sales,
       DENSE_RANK() OVER (
         PARTITION BY category
         ORDER BY sales DESC) AS rk
     FROM product_sales)
   SELECT * FROM r WHERE rk <= 3;

2. 02

   Month-over-month change

   First month is NULL: nothing before it.

   SELECT month, revenue,
     revenue - LAG(revenue) OVER (
       ORDER BY month) AS change
   FROM monthly_revenue;

Practise both live: rank inside each department and month-over-month change.

Your daily 2 hours

Same dataset all 14 days. Only the skill changes.

How to practise