SQL Interview Patterns: Top-N, Gaps & Islands, Pivots & More
FAANG SQL interviews ask the same 7 patterns: top-N per group, running totals, gaps & islands, pivots, retention cohorts, percentiles, hierarchical traversal. Master them and you'll pass nearly every data SQL screen in the industry.
Learning Objectives
After this lesson, you will be able to:
Solve top-N per group problems using ROW_NUMBER and RANK window functions
Identify consecutive sequences (gaps and islands) in time-series and event data
Compute running totals and cumulative sums with window functions
Pivot rows into columns and unpivot columns into rows using CASE and UNION
Use self-joins to compare rows within the same table (e.g., employee-manager hierarchies)
Write recursive CTEs for hierarchical data (org charts, category trees)
Deduplicate data while preserving the most recent or most relevant row
Apply conditional aggregation patterns that appear in FAANG interviews
Recognize which pattern an interview question maps to within 30 seconds
Your Reflection
Saves automatically
What’s one thing you learned? What’s still confusing?
Problem: Find the top 3 highest-paid employees in each department.
This is the single most common SQL interview question across FAANG companies. The trick: you cannot use LIMIT because it applies globally, not per group.
sql
-- Top 3 highest-paid employees per department
WITH ranked AS (
SELECT
department,
name,
salary,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS rank_in_dept
FROM employees
)
SELECT department, name, salary, rank_in_dept
FROM ranked
WHERE rank_in_dept <= 3
ORDER BY department, rank_in_dept;
ROW_NUMBER vs RANK vs DENSE_RANK
sql
-- Salary: 100K, 100K, 90K, 80K
-- ROW_NUMBER: 1, 2, 3, 4 (always unique, breaks ties arbitrarily)
-- RANK: 1, 1, 3, 4 (ties get same rank, next rank skips)
-- DENSE_RANK: 1, 1, 2, 3 (ties get same rank, next rank is consecutive)
SELECT
name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
RANK() OVER (ORDER BY salary DESC) AS rank_val,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_val
FROM employees;
Interview tip: Use ROW_NUMBER when you want exactly N results per group (even if there are ties). Use RANK or DENSE_RANK when ties should share the same position.
Loading visualization...
What Do You Think?
You use ROW_NUMBER() to find the top 1 employee per department. Two employees in Engineering both have $150K salary (the highest). How many Engineering rows appear in your result?
Exactly 1. ROW_NUMBER always assigns unique sequential integers -- it breaks ties arbitrarily (usually by physical row order). One employee gets rank 1 and the other gets rank 2. If you want BOTH tied employees, use RANK() or DENSE_RANK() instead, which assign the same rank to tied values.
Problem: Find consecutive login streaks for each user.
"Gaps and islands" identifies contiguous sequences in data. The "islands" are the consecutive groups; the "gaps" are the breaks between them.
sql
-- Find consecutive day login streaks
-- The trick: subtract a row number from each date.
-- Consecutive dates will produce the same "group key."
WITH login_dates AS (
SELECT DISTINCT user_id, DATE(login_time) AS login_date
FROM logins
),
islands AS (
SELECT
user_id,
login_date,
-- Consecutive dates minus consecutive row numbers = constant per island
DATE(login_date, '-' || ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY login_date
) || ' days') AS island_key
FROM login_dates
)
SELECT
user_id,
MIN(login_date) AS streak_start,
MAX(login_date) AS streak_end,
COUNT(*) AS streak_length,
CAST(JULIANDAY(MAX(login_date)) - JULIANDAY(MIN(login_date)) + 1 AS INTEGER) AS streak_days
FROM islands
GROUP BY user_id, island_key
HAVING streak_length >= 3 -- only show streaks of 3+ days
ORDER BY streak_length DESC;
Why this works: If a user logged in on Jan 1, 2, 3, 5, 6:
Row numbers: 1, 2, 3, 4, 5
Dates minus row numbers: Dec 31, Dec 31, Dec 31, Jan 1, Jan 1
The constant "Dec 31" groups the first island (Jan 1-3), "Jan 1" groups the second (Jan 5-6)
-- Running total of daily revenue
SELECT
DATE(order_date) AS order_day,
SUM(amount) AS daily_revenue,
SUM(SUM(amount)) OVER (
ORDER BY DATE(order_date)
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_revenue
FROM orders
GROUP BY DATE(order_date)
ORDER BY order_day;
-- Compare each day's revenue to the previous day
SELECT
DATE(order_date) AS order_day,
SUM(amount) AS daily_revenue,
LAG(SUM(amount), 1) OVER (ORDER BY DATE(order_date)) AS prev_day_revenue,
ROUND(100.0 * (SUM(amount) - LAG(SUM(amount), 1) OVER (ORDER BY DATE(order_date)))
/ LAG(SUM(amount), 1) OVER (ORDER BY DATE(order_date)), 1) AS day_over_day_pct
FROM orders
GROUP BY DATE(order_date)
ORDER BY order_day;
sql
-- Session duration: time between consecutive events
SELECT
user_id,
event_name,
event_time,
LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS next_event_time,
ROUND(
(JULIANDAY(LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time))
- JULIANDAY(event_time)) * 86400, 0
) AS seconds_to_next_event
FROM user_events
ORDER BY user_id, event_time;
Problem (Pivot): Turn rows into columns -- show monthly revenue as columns.
sql
-- Pivot: monthly revenue as columns (CASE WHEN approach)
SELECT
product_id,
SUM(CASE WHEN STRFTIME('%m', order_date) = '01' THEN revenue ELSE 0 END) AS jan,
SUM(CASE WHEN STRFTIME('%m', order_date) = '02' THEN revenue ELSE 0 END) AS feb,
SUM(CASE WHEN STRFTIME('%m', order_date) = '03' THEN revenue ELSE 0 END) AS mar,
SUM(CASE WHEN STRFTIME('%m', order_date) = '04' THEN revenue ELSE 0 END) AS apr,
SUM(CASE WHEN STRFTIME('%m', order_date) = '05' THEN revenue ELSE 0 END) AS may,
SUM(CASE WHEN STRFTIME('%m', order_date) = '06' THEN revenue ELSE 0 END) AS jun
FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2024-07-01'
GROUP BY product_id
ORDER BY product_id;
Problem (Unpivot): Turn columns into rows.
sql
-- Unpivot: survey responses stored as columns -> rows
-- Original: survey_id, q1_score, q2_score, q3_score
-- Target: survey_id, question, score
SELECT survey_id, 'Q1' AS question, q1_score AS score FROM surveys
UNION ALL
SELECT survey_id, 'Q2', q2_score FROM surveys
UNION ALL
SELECT survey_id, 'Q3', q3_score FROM surveys
ORDER BY survey_id, question;
What Do You Think?
You need to pivot daily status counts into columns: SELECT date, COUNT(CASE WHEN status='active' THEN 1 END) AS active, COUNT(CASE WHEN status='churned' THEN 1 END) AS churned FROM users GROUP BY date. If a date has 100 active and 0 churned users, what value appears in the 'churned' column?
It returns 0. When status is not 'churned', the CASE has no ELSE clause so it returns NULL. COUNT ignores NULLs, and COUNT of zero non-NULL values is 0. This is exactly why the COUNT(CASE WHEN ... THEN 1 END) pattern is the standard way to pivot in SQL -- it naturally returns 0 for empty groups instead of NULL.
Problem: Find employees who earn more than their manager.
sql
-- Self-join: compare employees to their managers
SELECT
e.name AS employee,
e.salary AS employee_salary,
m.name AS manager,
m.salary AS manager_salary,
e.salary - m.salary AS salary_difference
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary
ORDER BY salary_difference DESC;
sql
-- Self-join: find users who signed up on the same day
SELECT
a.user_id AS user_a,
b.user_id AS user_b,
DATE(a.created_at) AS signup_date
FROM users a
JOIN users b
ON DATE(a.created_at) = DATE(b.created_at)
AND a.user_id < b.user_id -- avoid duplicates (A,B) and (B,A)
ORDER BY signup_date;
Problem: Given an org chart, find all reports (direct and indirect) under a VP.
sql
-- Recursive CTE: traverse an org chart hierarchy
WITH RECURSIVE org_tree AS (
-- Base case: start with the VP
SELECT employee_id, name, manager_id, 1 AS level
FROM employees
WHERE name = 'VP Engineering'
UNION ALL
-- Recursive step: find all direct reports of current level
SELECT e.employee_id, e.name, e.manager_id, ot.level + 1
FROM employees e
JOIN org_tree ot ON e.manager_id = ot.employee_id
)
SELECT
level,
name,
employee_id,
manager_id
FROM org_tree
ORDER BY level, name;
sql
-- Recursive CTE: category breadcrumbs (e-commerce)
-- categories: id, name, parent_id
WITH RECURSIVE breadcrumb AS (
SELECT id, name, parent_id, name AS full_path
FROM categories
WHERE parent_id IS NULL -- root categories
UNION ALL
SELECT c.id, c.name, c.parent_id,
b.full_path || ' > ' || c.name AS full_path
FROM categories c
JOIN breadcrumb b ON c.parent_id = b.id
)
SELECT id, name, full_path
FROM breadcrumb
ORDER BY full_path;
-- Output: "Electronics > Computers > Laptops > Gaming Laptops"
Problem: A user event table has duplicate rows. Keep only the most recent event per user.
sql
-- Method 1: ROW_NUMBER (most common, most flexible)
WITH ranked AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY user_id, event_type
ORDER BY event_time DESC -- keep the most recent
) AS rn
FROM user_events
)
SELECT * FROM ranked WHERE rn = 1;
-- Method 2: GROUP BY with MAX (simpler but less flexible)
SELECT
user_id,
event_type,
MAX(event_time) AS latest_event_time
FROM user_events
GROUP BY user_id, event_type;
-- Method 3: DELETE duplicates (when you actually want to clean the table)
DELETE FROM user_events
WHERE rowid NOT IN (
SELECT MIN(rowid)
FROM user_events
GROUP BY user_id, event_type, DATE(event_time)
);
This pattern combines everything -- it is the kind of query that separates "good" from "great" in interviews.
sql
-- Product metrics dashboard (Amazon-style interview question)
-- "For each product, show: total orders, revenue, return rate,
-- repeat customer rate, and average time between orders"
WITH order_metrics AS (
SELECT
product_id,
customer_id,
order_date,
amount,
returned,
ROW_NUMBER() OVER (
PARTITION BY product_id, customer_id
ORDER BY order_date
) AS purchase_number,
LAG(order_date) OVER (
PARTITION BY product_id, customer_id
ORDER BY order_date
) AS prev_order_date
FROM orders
)
SELECT
product_id,
COUNT(*) AS total_orders,
COUNT(DISTINCT customer_id) AS unique_customers,
ROUND(SUM(amount), 2) AS total_revenue,
ROUND(AVG(amount), 2) AS avg_order_value,
-- Return rate
ROUND(100.0 * SUM(CASE WHEN returned = 1 THEN 1 ELSE 0 END) / COUNT(*), 1) AS return_rate_pct,
-- Repeat customer rate (customers with 2+ orders)
ROUND(100.0 * COUNT(DISTINCT CASE WHEN purchase_number >= 2 THEN customer_id END)
/ COUNT(DISTINCT customer_id), 1) AS repeat_customer_rate,
-- Average days between repeat orders
ROUND(AVG(
CASE WHEN prev_order_date IS NOT NULL
THEN JULIANDAY(order_date) - JULIANDAY(prev_order_date)
END
), 1) AS avg_days_between_orders
FROM order_metrics
GROUP BY product_id
ORDER BY total_revenue DESC;
Here is a quick reference for the most common patterns. When you see a problem in an interview, map it to one of these:
Problem Type
Key Technique
Signal Words
Top N per group
ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)
"top", "highest", "per category", "in each"
Consecutive sequences
ROW_NUMBER subtraction trick (gaps and islands)
"consecutive", "streak", "continuous", "gaps"
Running totals
SUM() OVER (ORDER BY ... ROWS UNBOUNDED PRECEDING)
"cumulative", "running total", "so far"
Period comparison
LAG() / LEAD() window functions
"previous", "next", "day-over-day", "change from"
Rows to columns
CASE WHEN inside aggregation
"pivot", "crosstab", "side by side"
Hierarchy traversal
Recursive CTE
"org chart", "tree", "parent-child", "all levels"
Remove duplicates
ROW_NUMBER() with rn = 1
"deduplicate", "most recent", "latest only"
Compare within table
Self-join (table aliased twice)
"compare to manager", "same day", "pairs"
Try it! Pick any three patterns above and try to write the queries from memory without looking at the examples. This is the best way to prepare -- interviewers expect you to write these patterns fluently, not look them up.
You need the top 3 products by revenue in each category. Two products in 'Electronics' are tied at #3. You want BOTH to appear. Which function should you use?
You now have the complete SQL toolkit -- from basic SELECT to advanced interview patterns. Practice these patterns until they are second nature. In real interviews, you will not have time to derive solutions from scratch; you need to recognize the pattern and apply the template. The best preparation is writing each pattern 3-5 times from memory.
What's next: You finished the SQL track. 13 lessons, every pattern, every trap, every interview template. Put it to work: write SQL against a real dataset (try the pgexercises.com practice DB, or pull a Kaggle CSV into DuckDB), build a dashboard with Metabase, or take a real dbt model and refactor it. The road from "knows SQL" to "ships analytics at FAANG" runs entirely through practice on real data — and you now have every tool you need.