Data Collection (SQL)

Writing highly efficient, complex SQL queries without relying on iterative trial-and-error — window functions, CTEs, aggregation correctness, and query optimization.

Key Takeaways

Window functions (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD) are the most tested SQL concept in DS assessments.
ROW_NUMBER() always produces unique ranks; RANK() skips after ties; DENSE_RANK() never skips.
CTEs (WITH clauses) are not a performance optimization — they improve readability and logic decomposition.
SQL execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Filter early with WHERE, not HAVING.
COUNT(*) counts all rows including NULLs; COUNT(column) counts only non-NULL values.
Fan-out (Cartesian product) from 1-to-many joins silently inflates aggregations — always check row counts.

1. Window Functions

Window functions compute values across a set of rows related to the current row without collapsing the result set like GROUP BY does.

Ranking Functions

Function	Ties Handled	Example Result (scores: 90, 90, 85)
`ROW_NUMBER()`	Arbitrary unique rank	1, 2, 3
`RANK()`	Same rank; next rank skips	1, 1, 3
`DENSE_RANK()`	Same rank; next rank does not skip	1, 1, 2

-- Top 3 customers by spend per month
WITH ranked AS (
  SELECT
    customer_id,
    DATE_TRUNC('month', txn_date)  AS month,
    SUM(amount)                    AS total_spend,
    ROW_NUMBER() OVER (
      PARTITION BY DATE_TRUNC('month', txn_date)
      ORDER BY SUM(amount) DESC
    ) AS rn
  FROM transactions
  GROUP BY customer_id, DATE_TRUNC('month', txn_date)
)
SELECT customer_id, month, total_spend
FROM ranked
WHERE rn <= 3;

Offset Functions: LAG and LEAD

-- Month-over-month spend change per customer
SELECT
  customer_id,
  txn_month,
  monthly_spend,
  LAG(monthly_spend, 1) OVER (
    PARTITION BY customer_id
    ORDER BY txn_month
  )                                           AS prev_month_spend,
  monthly_spend - LAG(monthly_spend, 1) OVER (
    PARTITION BY customer_id
    ORDER BY txn_month
  )                                           AS mom_change
FROM monthly_summary;

Rolling Aggregates

-- 7-day rolling average of daily transaction amounts
SELECT
  txn_date,
  daily_amount,
  SUM(daily_amount) OVER (
    ORDER BY txn_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) / 7.0 AS rolling_avg_7d
FROM daily_totals
ORDER BY txn_date;

2. Advanced Joins & Aggregations

Join Types

Join Type	Returns	Common Pitfall
`INNER JOIN`	Rows with matches in both tables	Silently drops unmatched rows from both sides
`LEFT JOIN`	All left rows + matched right rows (NULL if no match)	Forgetting NULLs on right side in WHERE filters
`FULL OUTER JOIN`	All rows from both tables	Many NULLs; use COALESCE to handle gracefully
`CROSS JOIN`	Cartesian product	Explosive row growth — intentional use only

Fan-out Problem

Joining a 1-to-many relationship before aggregating multiplies rows, inflating SUM() and COUNT(). Always verify row counts after joins:

-- Safe pattern: aggregate first, then join
WITH customer_totals AS (
  SELECT customer_id, SUM(amount) AS total_spend
  FROM transactions
  GROUP BY customer_id
)
SELECT c.name, ct.total_spend
FROM customers c
JOIN customer_totals ct ON c.customer_id = ct.customer_id;

NULL Handling

-- COALESCE: return first non-NULL value
SELECT COALESCE(phone_mobile, phone_home, 'No phone on file') AS contact_phone
FROM customers;

-- NULLIF: return NULL if two values are equal (avoids divide-by-zero)
SELECT total_revenue / NULLIF(total_orders, 0) AS avg_order_value
FROM order_summary;

3. CTEs and Query Structure

CTEs (WITH clauses) decompose complex logic into named, readable steps. They are not materialized by default in most engines — they do not inherently improve performance but dramatically improve readability.

-- Multi-step CTE: identify high-value customers with recent churn risk
WITH monthly_spend AS (
  SELECT
    customer_id,
    DATE_TRUNC('month', txn_date) AS month,
    SUM(amount)                   AS spend
  FROM transactions
  GROUP BY customer_id, DATE_TRUNC('month', txn_date)
),
ranked_months AS (
  SELECT
    customer_id,
    month,
    spend,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY month DESC) AS recency_rank
  FROM monthly_spend
),
latest_spend AS (
  SELECT customer_id, spend AS last_month_spend
  FROM ranked_months
  WHERE recency_rank = 1
)
SELECT
  c.customer_id,
  c.segment,
  ls.last_month_spend
FROM customers c
JOIN latest_spend ls ON c.customer_id = ls.customer_id
WHERE c.segment = 'High Value'
  AND ls.last_month_spend < 100
ORDER BY ls.last_month_spend;

4. Query Optimization

Rule	Why It Matters
Filter with `WHERE`, not `HAVING`	WHERE reduces rows before aggregation; HAVING filters after GROUP BY has already processed all rows
Avoid `SELECT *`	Forces the engine to read all columns; increases I/O and network transfer
Filter on indexed/partition columns	Enables partition pruning in columnar stores (Redshift, BigQuery, Snowflake)
Push aggregations as early as possible	Reduces row count before joins, avoiding fan-out
Use `EXPLAIN` / `EXPLAIN ANALYZE`	Reveals sequential scans, expensive sorts, and missing index usage

Execution order to memorize: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

This is why you cannot reference a SELECT alias in a WHERE clause — WHERE executes before SELECT.

Practice Questions

Q1: Write a query to find the top 3 customers by spend in each month. Use window functions.

Use ROW_NUMBER() OVER (PARTITION BY month ORDER BY spend DESC) in a CTE, then filter WHERE rn <= 3 in the outer query.

WITH ranked AS (
  SELECT
    customer_id,
    DATE_TRUNC('month', txn_date)  AS month,
    SUM(amount)                    AS total_spend,
    ROW_NUMBER() OVER (
      PARTITION BY DATE_TRUNC('month', txn_date)
      ORDER BY SUM(amount) DESC
    ) AS rn
  FROM transactions
  GROUP BY customer_id, DATE_TRUNC('month', txn_date)
)
SELECT customer_id, month, total_spend
FROM ranked
WHERE rn <= 3;

Use ROW_NUMBER (not RANK) so you always get exactly 3 rows per month even in the presence of ties.

Q2: What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK() when two rows have the same value?

Given scores: 90, 90, 85:

ROW_NUMBER: always unique — 1, 2, 3 (arbitrary tiebreak).
RANK: ties get the same rank, next rank skips — 1, 1, 3.
DENSE_RANK: ties get the same rank, next rank does not skip — 1, 1, 2.

Use ROW_NUMBER when you need exactly N results. Use DENSE_RANK when ties should share a rank without gaps. Use RANK when gaps are acceptable and meaningful (e.g., leaderboards).

Q3: Write a query to compute a 7-day rolling average of daily transaction amounts.

SELECT
  txn_date,
  daily_amount,
  SUM(daily_amount) OVER (
    ORDER BY txn_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) / 7.0 AS rolling_avg_7d
FROM daily_totals
ORDER BY txn_date;

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW includes the current day plus the 6 prior days — exactly 7 rows. Use ROWS (not RANGE) to avoid grouping duplicate dates together.

Q4: Why should you filter with WHERE instead of HAVING when possible?

WHERE filters rows before aggregation — fewer rows enter the GROUP BY, which reduces work and improves performance. HAVING filters after GROUP BY, so the engine has already computed aggregates for every group before discarding unwanted ones.

HAVING is only necessary when filtering on an aggregated column, e.g.:

-- Correct: filter on aggregate requires HAVING
SELECT customer_id, SUM(amount) AS total
FROM transactions
GROUP BY customer_id
HAVING SUM(amount) > 1000;

-- Also correct: filter on raw column uses WHERE (faster)
SELECT customer_id, SUM(amount) AS total
FROM transactions
WHERE txn_date >= '2024-01-01'   -- filter before GROUP BY
GROUP BY customer_id
HAVING SUM(amount) > 1000;

Action Items

Write a query to find the top 3 transactions per user per month using window functions — time yourself to under 5 minutes.
Write a query to calculate a 7-day rolling average and a 30-day rolling average in the same SELECT.
Practice identifying fan-out bugs: join two tables with a 1-to-many relationship, check row counts before and after.
Complete 5 "Hard" SQL questions on LeetCode focusing on window functions.
Memorize the SQL execution order (FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT) and explain why SELECT aliases are unavailable in WHERE.