Analytical SQL: Windows, CTEs & Interview Patterns

Build the SQL patterns most useful for analytics, Data Science and technical interviews.

Basic SQL retrieves data. Analytical SQL answers questions across groups, sequences and time.

TipKey ideas

CTE subquery window PARTITION BY ROW_NUMBER RANK LAG LEAD running total rolling average date arithmetic

The pattern worth mastering

SELECT
    employee_id,
    department,
    salary,
    RANK() OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS salary_rank
FROM employees;

Unlike GROUP BY, a window function can calculate across related rows without collapsing them into one row per group.

Five interview families

1. Top N per group
2. Consecutive events / gaps
3. Running and rolling metrics
4. First / last / previous / next event
5. Cohorts, retention and funnels

Learn patterns, then vary the problem.

References

PostgreSQL Window Functions:
https://www.postgresql.org/docs/current/tutorial-window.html

PostgreSQL WITH / CTE queries:
https://www.postgresql.org/docs/current/queries-with.html

HackerRank SQL:
https://www.hackerrank.com/domains/sql

LeetCode Database:
https://leetcode.com/problemset/database/

DS bridge

SQL is often where a model’s dataset is actually created:

warehouse
   ↓ SQL
training table
   ↓ Python/R
model

A beautiful model trained on a badly joined table is still a bad model.

WarningAlways check the grain

Before joining, state what one row represents in each table. Many analytical errors are accidental many-to-many joins, not mathematical errors.

Interview habit

Before typing SQL, say:

“I need one row per ___.”

That sentence often determines the entire query.