Module 2: SQL

This is the meatiest module in the Data Analyst track, so I’ll go topic by topic with annotated code and the talking points that separate “knows SQL” from “architect-level SQL.”

---

1. SELECT, Filtering, Sorting, Aggregation Fundamentals

The senior-level point here isn’t the syntax — it’s knowing the logical order of execution, because that’s what explains every “why doesn’t this work” SQL bug:

FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT/OFFSET

This ordering is why:

• You can’t filter on an aggregate in WHERE (it doesn’t exist yet) — you need HAVING
• You can’t reference a SELECT alias in WHERE (most engines) but you can in ORDER BY
• WHERE filters rows before grouping (cheap, early); HAVING filters groups after aggregation (expensive, late) — so always push filters into WHERE when possible, even if logically they “feel” like a HAVING.

SELECT
    customer_id,
    COUNT(*) AS order_count,
    SUM(order_amount) AS total_spend
FROM orders
WHERE order_date >= '2026-01-01'        -- filters rows first (cheap)
GROUP BY customer_id
HAVING SUM(order_amount) > 1000          -- filters groups after aggregation
ORDER BY total_spend DESC
LIMIT 10;

Interview talking point: “A common mistake junior analysts make is filtering on an aggregate in WHERE — it’ll throw an error — or pushing a filterable WHERE condition into HAVING, which forces the engine to aggregate rows it should’ve discarded early. I always ask: can this filter happen before grouping? If yes, it belongs in WHERE.”

NULL handling nuance (frequently tested): COUNT(*) counts all rows; COUNT(column) skips NULLs; aggregates like SUM/AVG silently ignore NULLs (not treat them as 0) — this trips people up in average calculations where NULL ≠ 0.

---

2. Joins — Inner / Left / Right / Full / Self

Join type	Returns	When to use
INNER	Only rows with matches in both tables	Default for “give me records that definitely relate” — e.g., orders that have a valid customer
LEFT	All rows from left + matches from right (NULLs if no match)	“Keep everything from my anchor table” — e.g., all customers, even those with zero orders (classic for finding non-converters)
RIGHT	Mirror of LEFT	Rare in practice — most people just flip table order and use LEFT; RIGHT JOIN existing in a codebase is often a readability smell
FULL OUTER	All rows from both, matched where possible	Reconciliation tasks — e.g., comparing two systems’ records to find mismatches on both sides
SELF	Table joined to itself	Hierarchies (employee → manager), sequential comparisons (this row vs. previous row before window functions existed)

-- LEFT JOIN to find customers with NO orders (classic interview question)
SELECT c.customer_id, c.customer_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;          -- the "anti-join" pattern

-- SELF JOIN for employee-manager hierarchy
SELECT e.employee_name, m.employee_name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

The anti-join pattern is the #1 join-related interview question — “find customers who never ordered,” “find products never sold,” “find users who signed up but never logged in.” The pattern is always: LEFT JOIN + WHERE right.key IS NULL.

Talking point on join cardinality: “Before writing a join, I always ask: what’s the cardinality relationship — one-to-one, one-to-many, many-to-many? A many-to-many join without an intermediate aggregation is the most common cause of silently duplicated/inflated totals in production dashboards — the query runs fine, returns no error, and just quietly multiplies your revenue numbers.” This is genuinely one of the most common real-world bugs and a great thing to volunteer in an interview.

---

3. Window Functions — RANK, LEAD/LAG, Running Totals, Moving Averages

Window functions are the single highest-leverage SQL topic for analyst interviews because they replace what used to require self-joins or subqueries, and they signal real fluency.

Syntax anatomy:

function() OVER (PARTITION BY ... ORDER BY ... [frame clause])

RANK / DENSE_RANK / ROW_NUMBER — the classic “who’s confused which one” interview trap:

SELECT
    customer_id,
    order_amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_amount DESC) AS rn,
    RANK()       OVER (PARTITION BY customer_id ORDER BY order_amount DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY order_amount DESC) AS drnk
FROM orders;

• ROW_NUMBER — always unique, no ties (1,2,3,4)
• RANK — ties share rank, gaps after (1,2,2,4)
• DENSE_RANK — ties share rank, no gaps (1,2,2,3)

Interview talking point: “If asked for the ‘top 3 orders per customer,’ the right tool is ROW_NUMBER if you want exactly 3 regardless of ties, or RANK if ties should all be included — that distinction is exactly what interviewers are probing when they ask this.”

LEAD/LAG — comparing a row to the previous/next row without a self-join:

SELECT
    customer_id,
    order_date,
    order_amount,
    LAG(order_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order_amount,
    order_amount - LAG(order_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS delta
FROM orders;

Running totals and moving averages — frame clause is the key concept:

SELECT
    order_date,
    daily_revenue,
    SUM(daily_revenue) OVER (ORDER BY order_date) AS running_total,
    AVG(daily_revenue) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7day_avg
FROM daily_sales;

Talking point on frame clauses: “The default frame for ORDER BY without an explicit ROWS/RANGE clause is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — which behaves unexpectedly with tied ORDER BY values. I always specify the frame explicitly (ROWS BETWEEN…) when I need precise row-based windows like a 7-day moving average, rather than relying on the default.” This is a genuinely senior-level detail most candidates miss.

---

4. Subqueries vs CTEs vs Temp Tables

	Subquery	CTE (WITH)	Temp Table
Readability	Degrades fast when nested	High — named, sequential, top-to-bottom readable	High, but adds DDL overhead
Reusability in same query	No (re-evaluated/duplicated if reused)	Yes — referenced multiple times in same query	Yes, and across multiple queries in a session
Materialization	Engine-dependent, usually inlined	Engine-dependent — Postgres pre-17 always materialized CTEs; Postgres 17+ and most modern engines may inline them like subqueries	Always materialized (actual storage, actual stats)
Indexing	Not possible	Not possible	Possible — can index a temp table for a complex multi-step pipeline
Best for	Simple, one-off, single-use filters	Multi-step logical pipelines, recursive logic	Heavy reuse across many subsequent queries, very large intermediate datasets

Recursive CTE (commonly tested for hierarchy/graph problems):

WITH RECURSIVE org_chart AS (
    SELECT employee_id, employee_name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL                      -- anchor: the CEO
    UNION ALL
    SELECT e.employee_id, e.employee_name, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart ORDER BY level;

Talking point on the materialization myth: “There’s a common assumption that CTEs are always materialized as a performance optimization — that was true historically in Postgres before version 12, but modern Postgres can inline a CTE just like a subquery unless you force materialization with the MATERIALIZED keyword. I don’t assume CTE = automatic performance boundary anymore; I check the execution plan.” This is a great “I stay current” line for an interview.

---

5. GROUP BY / HAVING Nuances, Set Operations

GROUP BY nuances:

• Every non-aggregated column in SELECT must appear in GROUP BY (strict SQL standard — MySQL historically allowed exceptions, which caused production bugs when ported to Postgres/SQL Server).
• GROUP BY with multiple columns creates one group per combination — a frequent interview trap is expecting per-column subtotals (ROLLUP/CUBE territory).

-- ROLLUP for subtotals + grand total (common in BI reporting queries)
SELECT region, product_category, SUM(revenue)
FROM sales
GROUP BY ROLLUP(region, product_category);

Set operations:

Operator	Behavior	Note
UNION	Combines, removes duplicates	Forces a dedup pass — slower
UNION ALL	Combines, keeps duplicates	Always prefer this unless you specifically need dedup — major performance habit
INTERSECT	Rows present in both queries	Useful for “users who did A AND B”
EXCEPT (or MINUS in Oracle)	Rows in first query, not in second	Useful for “users who did A but NOT B” — funnel drop-off pattern

Talking point: “I default to UNION ALL over UNION unless duplicates are a real risk, because UNION forces an implicit DISTINCT across the full result set, which is an expensive sort/hash step that’s easy to forget about until it shows up in a slow query log.”

---

6. Query Optimization Basics

Indexes:

• A B-tree index speeds up equality and range lookups on the indexed column(s), at the cost of slower writes (every INSERT/UPDATE must also update the index).
• Column order in composite indexes matters — an index on (customer_id, order_date) serves queries filtering on customer_id alone or customer_id + order_date, but not order_date alone (left-prefix rule). This is a very commonly tested nuance.
• Indexes help filtering/sorting but don’t help if the query wraps the column in a function (WHERE YEAR(order_date) = 2026 can’t use a plain index on order_date; rewrite as a range: WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01').

Execution plans:

• EXPLAIN (or EXPLAIN ANALYZE in Postgres) shows whether the engine is doing a Seq Scan (full table scan — red flag on large tables) vs. an Index Scan/Index Only Scan (good), and reveals join strategy (Nested Loop vs. Hash Join vs. Merge Join).
• Talking point: “On a table with millions of rows, a Nested Loop join where I’d expect a Hash Join is usually a sign that the planner’s row-count estimate is wrong — often because table statistics are stale. I’d run ANALYZE on the table before assuming I need to rewrite the query.” This is a genuinely senior diagnostic instinct.

Avoiding SELECT *:

• Forces the engine to fetch all columns even when unused — defeats index-only scans (where the index alone could answer the query without touching the table) and increases I/O and network transfer unnecessarily.
• Also a maintainability hazard: schema changes (added columns) silently change downstream output shape.

---

7. Common SQL Interview Problems — Cohort, Retention, Funnel

These three patterns cover the vast majority of “real” analyst SQL interview questions, because they combine joins + window functions + date logic.

Cohort analysis (group users by signup month, track behavior over time):

WITH cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('month', MIN(signup_date)) AS cohort_month
    FROM users
    GROUP BY user_id
),
activity AS (
    SELECT
        c.cohort_month,
        DATE_TRUNC('month', a.activity_date) AS activity_month,
        COUNT(DISTINCT a.user_id) AS active_users
    FROM cohorts c
    JOIN user_activity a ON c.user_id = a.user_id
    GROUP BY 1, 2
)
SELECT
    cohort_month,
    activity_month,
    active_users,
    DATE_PART('month', age(activity_month, cohort_month)) AS months_since_signup
FROM activity
ORDER BY cohort_month, months_since_signup;

Retention (N-day / N-month retention rate):

WITH first_seen AS (
    SELECT user_id, MIN(activity_date) AS signup_date
    FROM user_activity
    GROUP BY user_id
)
SELECT
    f.signup_date,
    COUNT(DISTINCT f.user_id) AS cohort_size,
    COUNT(DISTINCT CASE WHEN a.activity_date = f.signup_date + INTERVAL '1 day' THEN a.user_id END) AS day1_retained,
    ROUND(
        COUNT(DISTINCT CASE WHEN a.activity_date = f.signup_date + INTERVAL '1 day' THEN a.user_id END)::NUMERIC
        / COUNT(DISTINCT f.user_id), 2
    ) AS day1_retention_rate
FROM first_seen f
LEFT JOIN user_activity a ON f.user_id = a.user_id
GROUP BY f.signup_date;

Funnel analysis (conversion through sequential steps):

WITH funnel AS (
    SELECT
        user_id,
        MAX(CASE WHEN event_name = 'viewed_product' THEN 1 ELSE 0 END) AS step1,
        MAX(CASE WHEN event_name = 'added_to_cart' THEN 1 ELSE 0 END) AS step2,
        MAX(CASE WHEN event_name = 'completed_purchase' THEN 1 ELSE 0 END) AS step3
    FROM events
    GROUP BY user_id
)
SELECT
    SUM(step1) AS viewed,
    SUM(CASE WHEN step1=1 THEN step2 ELSE 0 END) AS added_to_cart,
    SUM(CASE WHEN step1=1 AND step2=1 THEN step3 ELSE 0 END) AS purchased
FROM funnel;

Talking point that ties all three together: “Cohort, retention, and funnel queries are really the same underlying pattern — establishing an anchor event per user (signup, first activity, first funnel step), then joining forward in time and aggregating conditionally. Once you recognize that shape, you can solve almost any ‘how many users did X after Y’ interview question with the same template.” That’s the kind of pattern-recognition statement that signals real depth rather than memorized queries.