Y

SQL · Intermediate · 5 h

Window functions

Rankings, running totals, moving averages and period-over-period change without self-joins.

Why employers care: The line between junior and mid-level SQL in most interview loops. Top-N per group and day-over-day change come up constantly.

In the roadmaps for: Data Analyst, Data Scientist, Data Engineer, Software Engineer (Backend)

Lessons, in order

  1. SQL ROW_NUMBER, RANK and DENSE_RANK: top N per groupSolve top N per group, latest row per customer and Nth highest per group with ROW_NUMBER, RANK and DENSE_RANK, and know exactly what happens on ties.13 min
  2. SQL running total, moving average and LAGBuild running totals, 7-day moving averages, day-over-day and week-over-week change with SUM OVER, ROWS BETWEEN and LAG, and handle missing days the right way.14 min
  3. SQL percentiles and median: PERCENTILE_CONT and NTILECompute medians, p90 and p99 per group, customer deciles with NTILE and percentile ranks, in PostgreSQL, DuckDB and without any percentile function at all.13 min
  4. Remove duplicate rows in SQL with ROW_NUMBERFind exact and business-key duplicates, decide whether they are errors or real repeats, keep one row per key with ROW_NUMBER or DISTINCT ON, and stop them coming back.14 min

Next shelf: CTEs, dates, strings and analysis patterns