929 real SQL questions from reported data engineer loops, spanning all of FAANG plus Microsoft, Nvidia, Tesla, Uber, Airbnb, and hundreds more employers. The 20 below are solved in full: a live editor, a verified solution, and a note on where submissions usually go wrong.
SQL shows up in 95% of data engineer interview loops, and the round has a consistent shape: a small schema, a business question phrased the way a product manager would ask it in Slack, and 15 to 30 minutes to write a working query. Our catalog holds 929 such questions from reported data engineer loops. The 20 below are solved in full, 1 per company across Meta, Amazon, Apple, Netflix, Google, Microsoft, Nvidia, Tesla, Uber, Airbnb, and 10 more named employers, ordered easy to hard. Write each one against a real schema, get a verdict, and read a verified solution with a note on the mistake most candidates make.
Solve them here, or open any question's full problem page for hints, the worked walkthrough, and EXPLAIN on a passing run. When you finish the 20, the full catalog of 929 SQL interview questions continues with the same live validation.
Computed from the 929 SQL problems tagged by concept in our catalog, across the 176 employers they were reported from. Percentages are the share of those problems using each concept, so they overlap: a single question usually pulls in three or four at once.
| Concept | Share of problems | Employers | What comes up |
|---|---|---|---|
| Aggregation (GROUP BY, HAVING) | 64% (599) | 142 | Filtering groups after aggregation vs rows before it — the WHERE vs HAVING distinction. |
| Subqueries and EXISTS | 23% (216) | 102 | Correlated vs non-correlated cost, and why NOT EXISTS survives NULLs where NOT IN does not. |
| CTEs and query structure | 21% (199) | 88 | Readable multi-step transforms, and when to materialize instead of inlining. |
| JOINs and join cardinality | 21% (193) | 79 | INNER vs LEFT, self-joins, and the many-to-many fanout that silently doubles every downstream metric. |
| Deduplication and DISTINCT | 19% (172) | 74 | Finding and removing duplicate rows, and picking which row survives. |
| NULL semantics (COALESCE, NULLIF) | 18% (167) | 66 | Three-valued logic, guarded division, and NULLs that vanish from aggregates. |
| Date and time handling | 16% (148) | 72 | DATE_TRUNC bucketing, date arithmetic, and DATE vs TIMESTAMP boundary bugs. |
| Conditional aggregation (CASE WHEN) | 14% (133) | 60 | Pivoting rows to columns; the missing ELSE that turns an AVG into a silent 100%. |
| Window functions (ROW_NUMBER, RANK, LAG/LEAD) | 13% (120) | 58 | Top-N per group, dedup-latest, running totals, and period-over-period. The tier that decides senior rounds. |
| Query optimization and EXPLAIN plans | 13% (120) | 58 | Reading an EXPLAIN plan, indexing, and partition pruning on partitioned tables. |
| Set operations (UNION vs UNION ALL) | 3% (24) | 15 | The dedupe sort UNION hides, and why UNION ALL is the default worth defending. |
Concept tags come from the same catalog that powers the practice problems, so this table moves as the catalog grows. Counts recomputed hourly.
8 patterns cover roughly 90% of the SQL data engineers see in interviews. Each maps to questions on this page.
| Pattern | The canonical form | On this page |
|---|---|---|
| Top-N and leaderboards | RANK or DENSE_RANK when ties matter; LIMIT only when they cannot | Questions 3, 11, 15 |
| Dedup latest per key | ROW_NUMBER ordered by updated_at DESC with a composite tiebreaker | Question 1 |
| Conditional aggregation | SUM(CASE WHEN ...) for pivots and per-slice totals; mind the missing ELSE | Questions 4, 6, 7 |
| Anti-join | LEFT JOIN with IS NULL, or NOT EXISTS; never NOT IN on nullable columns | Questions 2, 20 |
| Rolling and running windows | Explicit ROWS BETWEEN frame clauses, never the RANGE default | Question 13 |
| Period-over-period and time-in-state | LAG or LEAD over an aggregated CTE with guarded division | Questions 9, 12, 14, 16, 18 |
| Gaps and islands | Date or row-number differencing to build a streak key | Question 19 |
| Point-in-time validity | Issued before, not expired as of, NULL end means still live | Questions 8, 10 |
Phone-screen warm-ups. Write them fast, and mention the edge case before the interviewer asks about it.
The release dashboard needs to reflect the current state of every service. Show each service alongside the latest version that was deployed to it.
SELECT svc_name, version
FROM (
SELECT svc_name, version,
ROW_NUMBER() OVER (PARTITION BY svc_name ORDER BY deploy_at DESC) AS rn
FROM deploy_logs
) sub
WHERE rn = 1
Which products in the catalog have never been ordered? Show product ID and product name for items with no matching transaction.
SELECT p.product_id, p.product_name FROM products p LEFT JOIN transactions t ON p.product_id = t.product_id WHERE t.transaction_id IS NULL
The ad ops team is auditing which impressions pulled in the most money. Surface the three highest-revenue impressions, each with when it occurred and the revenue it earned, biggest earner first.
SELECT impression_time, revenue FROM ad_impressions ORDER BY revenue DESC LIMIT 3
On-call wants every recorded error tagged with a severity label based on how often it has fired: 0 occurrences is NONE, 1 to 5 is LOW, 6 to 20 is MODERATE, 21 to 50 is HIGH, and anything above 50 is CRITICAL. Treat any error whose occurrence count was never recorded as CRITICAL, since on-call escalates anything it cannot size. Skip rows that aren't attributed to a service, and show each error type alongside its label.
SELECT
err_type,
CASE
WHEN count = 0 THEN 'NONE'
WHEN count BETWEEN 1 AND 5 THEN 'LOW'
WHEN count BETWEEN 6 AND 20 THEN 'MODERATE'
WHEN count BETWEEN 21 AND 50 THEN 'HIGH'
ELSE 'CRITICAL'
END AS severity_label
FROM err_tracks
WHERE svc_name IS NOT NULL
We keep two independent logs for the web product: one row per session in the session log and one row per page view in the page-view log, and a single person piles up many of each. For everyone carrying a real user id in either log, report their total time across all sessions rounded to the nearest minute together with how many different pages they opened.
SELECT
u.user_id,
COALESCE(ROUND(s.total_secs / 60.0), 0) AS total_minutes,
COALESCE(p.unique_content, 0) AS unique_content_count
FROM (
SELECT user_id FROM user_sessions
UNION
SELECT user_id FROM page_views
) u
LEFT JOIN (
SELECT user_id, SUM(session_duration_sec) AS total_secs
FROM user_sessions
GROUP BY user_id
) s ON u.user_id = s.user_id
LEFT JOIN (
SELECT user_id, COUNT(DISTINCT page_url) AS unique_content
FROM page_views
GROUP BY user_id
) p ON u.user_id = p.user_id
The merchandising team wants a per-product read on how much sales volume comes specifically from the 'Electronics' category. For every product, show the total transaction amount tied to Electronics, biggest first, and keep products that have never sold under Electronics in the list with a zero.
SELECT p.product_name,
COALESCE(SUM(CASE WHEN p.category = 'Electronics' THEN t.total_amount END), 0) AS electronics_total
FROM products p
LEFT JOIN transactions t ON p.product_id = t.product_id
GROUP BY p.product_id, p.product_name
ORDER BY electronics_total DESC, p.product_nameThe core of every data engineer loop: pivots, point-in-time audits, time-in-state, ties on the leaderboard, and the window frames that decide mid-level screens.
The finance team is reconciling cloud invoices and wants a monthly read on spend by provider, but the provider names arrive with inconsistent casing where 'aws' and 'AWS' both mean the same platform. Give the combined total for AWS, GCP, and Azure in each month, counting every billing entry, oldest month first.
SELECT STRFTIME('%Y-%m', bill_date) AS month,
SUM(CASE WHEN UPPER(provider) = 'AWS' THEN amount ELSE 0 END) AS aws_total,
SUM(CASE WHEN UPPER(provider) = 'GCP' THEN amount ELSE 0 END) AS gcp_total,
SUM(CASE WHEN UPPER(provider) = 'AZURE' THEN amount ELSE 0 END) AS azure_total
FROM cloud_costs
GROUP BY STRFTIME('%Y-%m', bill_date)
ORDER BY month
The security team is auditing which owners held a live API token on November 1 of 2026, and each qualifying owner should appear once. Different services write the status column inconsistently, so treat a token as enabled only when its status reads exactly as the lowercase word 'active'. A live token also had to be issued before that date and not yet expired, with a missing expiration date treated as still valid.
SELECT DISTINCT owner_id FROM api_tokens WHERE status = 'active' AND issued < '2026-11-01' AND (expires IS NULL OR expires > '2026-11-01') ORDER BY owner_id
Our pipeline tracks batch job state transitions: every time a job changes status, the system records the job ID, timestamp, and status (queued, running, completed). Calculate total hours all jobs spent in each status. Duration is the difference between the current status timestamp and the next status change. For each job's final status, assume it lasted 2 hours. Round to 2 decimal places.
WITH transitions AS (
SELECT job_id, status, started,
LEAD(started) OVER (PARTITION BY job_id ORDER BY started) AS next_started
FROM batch_jobs
)
SELECT status,
ROUND(SUM(
CASE
WHEN next_started IS NOT NULL
THEN (JULIANDAY(next_started) - JULIANDAY(started)) * 24
ELSE 2.0
END
), 2) AS total_hours
FROM transitions
GROUP BY status
We're auditing feature flags that got left on too long: surface the ones created more than 730 days before May 1, 2026. For each, show its name, owner, how many whole years it's been since creation, and whether it's still enabled, counting a flag with no updated timestamp as still on.
SELECT flag_name, owner,
CASE WHEN enabled = 1 OR updated IS NULL THEN 'Yes' ELSE 'No' END AS still_enabled,
CAST((JULIANDAY('2026-05-01') - JULIANDAY(created)) / 365 AS INTEGER) AS years_since_creation
FROM feat_flags
WHERE JULIANDAY('2026-05-01') - JULIANDAY(created) > 730
ORDER BY flag_name, owner, years_since_creation DESC, still_enabled
An SRE team is compiling a reliability leaderboard by region, where a probe's effective hours is its uptime minus a tenth of its latency, and a probe with no recorded latency counts as having zero latency. Total each region's effective hours and surface the three most reliable regions, with equal totals sharing a place.
WITH effective AS (
SELECT region,
SUM(uptime - COALESCE(latency, 0) / 10.0) AS total_effective_hours
FROM svc_health
GROUP BY region
),
ranked AS (
SELECT region,
total_effective_hours,
DENSE_RANK() OVER (ORDER BY total_effective_hours DESC) AS rnk
FROM effective
)
SELECT region, ROUND(total_effective_hours, 2) AS total_effective_hours
FROM ranked
WHERE rnk <= 3
ORDER BY total_effective_hours DESC
A billing-integrity team is chasing accidental recurring charges: a customer billed the same amount for the same product about a month after the last time, usually a duplicate subscription or a botched retry. Within each user and product pairing, compare every charge to the one immediately before it in time, and surface the charges that repeat the previous amount exactly and land 35 days or fewer after it.
WITH lagged AS (
SELECT *, LAG(total_amount) OVER (PARTITION BY user_id, product_id ORDER BY transaction_date) AS prev_amount, LAG(transaction_date) OVER (PARTITION BY user_id, product_id ORDER BY transaction_date) AS prev_date
FROM transactions
)
SELECT transaction_id, user_id, product_id, total_amount, transaction_date
FROM lagged
WHERE total_amount = prev_amount AND (julianday(transaction_date) - julianday(prev_date)) <= 35
ORDER BY transaction_date
The platform reliability team monitors latency trends per service. For each service's health check history, compute a 7-check rolling average of latency using the current check and the 6 checks immediately before it, ordered by check timestamp. Return the service name, check timestamp, raw latency, and the rolling average.
SELECT
svc_name,
checked,
latency,
AVG(latency) OVER (
PARTITION BY svc_name
ORDER BY checked
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS rolling_avg
FROM svc_health
ORDER BY svc_name, checkedThe senior tier: period-over-period, sweep lines, gaps and islands, and temporal anti-joins. Expect follow-ups on every choice you made.
A finance team tracks revenue momentum by watching how each month stacks up against the one before it. Total each month's transaction revenue and show how it moved, in percent, from the immediately preceding month.
WITH monthly AS (
SELECT STRFTIME('%Y-%m', transaction_date) AS month,
SUM(total_amount) AS revenue
FROM transactions
GROUP BY STRFTIME('%Y-%m', transaction_date)
),
sequenced AS (
SELECT month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_revenue
FROM monthly
)
SELECT month,
ROUND(revenue, 2) AS revenue,
CASE
WHEN prev_revenue IS NULL THEN NULL
WHEN prev_revenue = 0 THEN NULL
ELSE ROUND((revenue - prev_revenue) * 100.0 / prev_revenue, 2)
END AS pct_change
FROM sequenced
ORDER BY month
The attribution team uses a first-touch model: every conversion is credited to the very first ad impression the user ever received, regardless of whether they clicked. A user counts as converted if they appear in the transactions table. For each converted user, return their user_id, the ad_campaign from their earliest impression, and that impression_time.
WITH ranked AS (
SELECT
i.user_id,
i.ad_campaign,
i.impression_time,
ROW_NUMBER() OVER (
PARTITION BY i.user_id
ORDER BY i.impression_time
) AS rn
FROM ad_impressions i
WHERE EXISTS (
SELECT 1 FROM transactions t WHERE t.user_id = i.user_id
)
)
SELECT user_id, ad_campaign, impression_time
FROM ranked
WHERE rn = 1
A FinOps team benchmarks its cost models against the simplest possible forecast: whatever a month actually cost becomes next month's prediction. Total each month's real cloud spend, keeping only positive charges so that credits (posted as a negative amount) and unpriced items (a null amount) never distort the figure, and treat the prior month's total as the current month's forecast. Report each month's year-month, its actual cost, the forecast, and the absolute percent error of the forecast measured against that month's actual cost.
WITH monthly AS (
SELECT strftime('%Y-%m', bill_date) AS ym, SUM(amount) AS total_amount
FROM cloud_costs WHERE amount IS NOT NULL AND amount > 0
GROUP BY strftime('%Y-%m', bill_date)
),
ratios AS (
SELECT ym, total_amount, LAG(total_amount) OVER (ORDER BY ym) AS prev_amount FROM monthly
)
SELECT ym, total_amount AS actual_cost, prev_amount AS forecasted_cost,
CASE WHEN total_amount > 0 THEN ABS((prev_amount - total_amount) / total_amount) * 100 END AS pct_error
FROM ratios
WHERE prev_amount IS NOT NULL
ORDER BY ym
Determine the minimum number of parallel workers required to run all batch jobs without conflicts. Each job has a start and end timestamp and can overlap with others. Duplicate job entries should be counted once, and jobs missing start or end times should be excluded. Find the peak number of concurrently running jobs at any point.
WITH events AS (
SELECT started AS event_time, 1 AS delta
FROM (
SELECT DISTINCT job_id, started, ended
FROM batch_jobs
WHERE started IS NOT NULL AND ended IS NOT NULL
)
UNION ALL
SELECT ended AS event_time, -1 AS delta
FROM (
SELECT DISTINCT job_id, started, ended
FROM batch_jobs
WHERE started IS NOT NULL AND ended IS NOT NULL
)
),
running AS (
SELECT event_time,
SUM(delta) OVER (ORDER BY event_time, delta DESC) AS concurrent
FROM events
)
SELECT MAX(concurrent) AS min_workers
FROM running
Our pipeline tracks user events with timestamps. For each event type, calculate the total hours elapsed between consecutive events of the same type.
SELECT event_type, SUM(hours_diff) AS total_hours
FROM (
SELECT
event_type,
(JULIANDAY(event_timestamp) - JULIANDAY(
LAG(event_timestamp) OVER (
PARTITION BY event_type
ORDER BY event_timestamp
)
)) * 24 AS hours_diff
FROM event_data
) gaps
WHERE hours_diff IS NOT NULL
GROUP BY event_type
Find periods where total cloud spending increased for 2 consecutive billing periods. Return the starting bill date of each growth streak and its length.
WITH monthly AS (
SELECT bill_date, SUM(amount) AS total_amount FROM cloud_costs WHERE amount IS NOT NULL GROUP BY bill_date
),
with_lag AS (
SELECT bill_date, total_amount,
LAG(total_amount) OVER (ORDER BY bill_date) AS prev_amount,
ROW_NUMBER() OVER (ORDER BY bill_date) AS rn
FROM monthly
),
increasing AS (
SELECT bill_date, rn FROM with_lag WHERE total_amount > prev_amount
),
streak_groups AS (
SELECT bill_date, rn - ROW_NUMBER() OVER (ORDER BY bill_date) AS grp FROM increasing
)
SELECT MIN(bill_date) AS start_date, COUNT(*) AS streak_len
FROM streak_groups
GROUP BY grp
HAVING COUNT(*) >= 2
ORDER BY start_date
The product team is measuring organic adoption of the new editor, which shows up in the page views under the URL 'new_editor' while the old one appears as 'classic_editor'. Find the users who reached the new editor without ever having opened the classic editor before their first new editor visit.
WITH first_new AS (
SELECT user_id, MIN(viewed_at) AS first_new_date
FROM page_views
WHERE page_url = 'new_editor'
GROUP BY user_id
)
SELECT fn.user_id
FROM first_new fn
WHERE NOT EXISTS (
SELECT 1
FROM page_views ua
WHERE ua.user_id = fn.user_id
AND ua.page_url = 'classic_editor'
AND ua.viewed_at < fn.first_new_date
)The verbal questions that fill the gaps between coding prompts. One wrong answer here costs more than a slow query.
WHERE filters rows before grouping, HAVING filters groups after aggregation. The practical consequence: an aggregate like SUM can only be filtered in HAVING, and pushing every possible predicate into WHERE first is both correct and cheaper, because it shrinks the data before the group step.
All 3 number rows within a window. ROW_NUMBER forces unique positions, arbitrarily on ties unless you add a tiebreaker. RANK gives ties the same position and skips the next numbers. DENSE_RANK gives ties the same position and skips nothing. Top-N answers change depending on which you pick, which is why interviewers ask.
NOT IN compiles to a chain of not-equals comparisons, and any comparison with NULL is unknown, so one NULL in the subquery makes every row fail the predicate and the query silently returns nothing. NOT EXISTS just checks for the presence of a matching row, so NULLs in the compared column never poison it.
UNION deduplicates the combined result, which forces a sort or hash across the whole set. UNION ALL just concatenates. Default to UNION ALL unless you have a stated reason to dedupe: it is semantically explicit and avoids a hidden performance cliff on large sets.
COUNT(*) counts rows. COUNT(column) counts rows where that column is not NULL, and COUNT(DISTINCT column) counts distinct non-NULL values. The gap between COUNT(*) and COUNT(column) is a quick null-rate probe, a trick worth mentioning in a data-quality discussion.
A CTE buys readability and reuse within one statement, and modern planners usually inline it. A temp table materializes: worth it when the intermediate result is reused across statements, needs an index, or the planner keeps re-executing an expensive subquery. Naming that materialization tradeoff is the senior version of this answer.
ROWS counts physical rows; RANGE groups peer rows with equal ORDER BY values and includes them together. The default frame with an ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so a rolling average written without an explicit ROWS clause is a running average with tie-merging. Always write the frame you mean.
A subquery that references the outer row, so it conceptually re-executes per row. Planners often rewrite it as a join, but when they cannot, an O(n) query becomes O(n squared). The interview follow-up is usually to rewrite one as a window function or join, so practice both directions.
A many-to-many join. If the join key is not unique on either side, rows fan out and every aggregate downstream inflates. The fix is to aggregate or dedupe to the correct grain before joining. Saying the word grain out loud is half the answer.
ROW_NUMBER over PARTITION BY the natural key, ordered by the survivorship rule, then keep rn = 1. For detection alone, GROUP BY the key with HAVING COUNT(*) > 1. The interviewer usually pushes on which row survives, which is a business rule, not a SQL detail, and noticing that is the point.
Finding runs of consecutive values: consecutive login days, unbroken subscription months, contiguous active periods. The standard solution differences a row number against the sequence itself, so ROW_NUMBER() OVER (PARTITION BY user ORDER BY day) subtracted from the date yields a constant per streak. Group by that constant and each group is one island. Derive it once rather than memorizing it, because the tiebreaker and the gap tolerance change per prompt.
When the depth is unknown at write time: walking a manager hierarchy of arbitrary height, exploding a bill of materials, or generating a date spine. A recursive CTE has an anchor member and a recursive member joined back to the CTE, and it terminates when the recursive member returns no rows. A fixed number of self-joins is the right answer when the depth is known and small; recursion is for when it is not.
Both put a fact table at the center. A star schema keeps dimensions denormalized in one table each, so queries join once per dimension. A snowflake schema normalizes dimensions into sub-tables, saving storage and enforcing integrity at the cost of extra joins. OLAP analytical warehouses usually favor the star: fewer joins, simpler queries, and storage is cheap relative to query time.
Instead of overwriting a changed attribute, insert a new row and close the old one. Each row carries valid_from, valid_to, and an is_current flag; the update sets the prior row's valid_to to the change timestamp and flips is_current to false. Point-in-time queries then join on the fact's event date falling between valid_from and valid_to. SCD Type 1 overwrites and keeps no history, which is the contrast interviewers want stated.
Idempotency means re-running the same load produces the same result rather than duplicating rows. In SQL that is usually a MERGE keyed on a business key, or a delete-then-insert scoped to the partition being rebuilt, both wrapped in one transaction. The anti-pattern is a bare INSERT on a retry path: the first partial run leaves rows behind and the retry doubles them. Deterministic partition boundaries matter as much as the write itself.
Read it inside out, starting at the leaves. Look for sequential scans on large tables where an index exists, nested loops driven by a big outer relation, and a large gap between estimated and actual row counts, which signals stale statistics. EXPLAIN ANALYZE runs the query and reports real timings per node, so the node consuming the most actual time is the one to fix. Add the index or rewrite the join order, then re-read the plan.
Partition pruning is the planner skipping partitions that cannot match the query's predicate, so a filter on the partition key reads one day instead of five years. It fails when the predicate wraps the partition column in a function, compares it against a non-constant the planner cannot resolve, or uses a type that forces an implicit cast. Filter on the raw partition column with a literal or a bound parameter and pruning holds.
Skew is one join key holding a disproportionate share of rows, so a single task processes most of the data while the rest idle and the job stalls at 99%. Detect it by counting rows per key on both sides. Mitigations: broadcast the small side to avoid the shuffle entirely, salt the hot key by appending a random suffix and joining on the salted key before re-aggregating, or split the hot keys into a separate job.
A clustered index defines the physical order of rows on disk, so there can be exactly one per table and range scans on it are sequential. A non-clustered index is a separate structure holding the key plus a pointer back to the row, so there can be many, and a lookup that needs columns outside the index pays an extra fetch. Covering the query with an included column avoids that fetch.
Atomicity: a transaction fully commits or fully rolls back. Consistency: it moves the database from one valid state to another, respecting constraints. Isolation: concurrent transactions do not observe each other's intermediate state, tunable through isolation levels. Durability: once committed, the write survives a crash. The follow-up is usually about isolation levels and which anomalies each one permits.
DELETE removes rows one at a time, is transactional, fires triggers, and can carry a WHERE clause. TRUNCATE deallocates whole pages, so it is far faster, resets identity sequences, and cannot be filtered. DROP removes the table definition itself. The practical interview point is that TRUNCATE is minimally logged and hard to undo, so it belongs in rebuild jobs, not in production cleanup with a predicate.
Normalization removes redundancy across forms: 1NF requires atomic values, 2NF removes partial dependencies on a composite key, 3NF removes transitive dependencies. It protects write integrity. Analytical warehouses then denormalize deliberately, collapsing dimensions into wide tables so reads join less. Both are correct in their own layer, and naming which layer you are designing for is the answer interviewers want.
It produces the Cartesian product, every row on the left paired with every row on the right. Usually it is a bug from a missing join predicate. Legitimately, it generates dense grids: crossing a date spine with a dimension list so every combination exists before a LEFT JOIN fills in the sparse measures, which is how you report zeros for days that had no activity.
Both enforce uniqueness. A primary key is one per table, cannot be NULL, and is the row's identity. A unique constraint can be declared many times per table and typically permits one NULL, since NULL is not equal to itself under three-valued logic. In a warehouse the primary key is usually a surrogate key issued by the warehouse, with the source system's natural key kept as a unique constraint.
Written order is not evaluation order. Evaluation runs FROM and JOIN, then WHERE, GROUP BY, HAVING, window functions, SELECT, DISTINCT, ORDER BY, and finally LIMIT. This explains the two rules candidates trip on: a SELECT alias is not visible to WHERE because SELECT has not run yet, and a window function cannot appear in the same query's WHERE because windows are computed after filtering.
Conditional aggregation: SUM(CASE WHEN category = 'x' THEN amount END) as one column per category, grouped by the row key. Some dialects offer a PIVOT operator, but the CASE form is portable and is what interviewers expect. The trap is omitting the ELSE and then using AVG, which drops non-matching rows from the denominator instead of counting them as zero.
A view is a stored query, re-executed on every read, so it is always current and costs full computation each time. A materialized view stores the computed result, so reads are cheap but the data is as stale as the last refresh. The engineering question is refresh strategy: full rebuild versus incremental, and what staleness the consumer tolerates.
Four capabilities that separate offers from rejections, roughly in the order worth building them. Not a syllabus: work them in whatever order matches where you are weakest.
SELECT, WHERE, GROUP BY with HAVING, INNER versus LEFT JOIN when the right side can be missing. These should land in 5 to 8 minutes without deliberation. The point is not the syntax, it is that a warm-up question costs you no clock, so the whole round is spent on the question that actually decides it.
Multi-table joins, CASE inside SUM and AVG, date bucketing, and the many-to-many duplication trap. Most phone screens turn on this tier, and most failures here are silent: a number that looks plausible and is wrong.
ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, running totals, rolling frames, dedup-latest. This is the capability candidates most often underweight and most often regret. Every hard question on this page except the temporal anti-join is a window pattern.
Mixed problems on a 25-minute timer while narrating tradeoffs as you type: why DENSE_RANK, where the NULLs go, what the query costs. Then run mock rounds against a deliberately vague prompt, because solving a stated problem and handling an interview are different skills, and only one of them gets you the offer.
From submissions across the catalog, these are the recurring failure modes, not syntax errors.
ORDER BY metric LIMIT 1 passes a single fixture and fails any dataset where two rows tie at the top. Rank explicitly and decide tie behavior on purpose. This is the single most common failure across live runs.
Non-matching rows become NULL, AVG silently drops them from the denominator, and the rate reads as 100%. The bug reads as correct on eyeball review, which is exactly why it survives to production.
One NULL in the subquery and the whole result is empty, with no error. NOT EXISTS is the reflex to build. Interviewers plant this deliberately.
Omitting ROWS BETWEEN turns a rolling average into a running average and merges tied ORDER BY peers. If the prompt says rolling, the answer contains the word ROWS.
Joining a one-to-many and then summing inflates every number. Aggregate to the join grain first, then join. Interviewers call this the fanout question; question 5 is built around avoiding it.
Filtering on an ID or date you saw in the preview passes that fixture and nothing else. The 10-seed replay catches it the same way a code reviewer would.
A 45-minute SQL round opens with one easy or medium warm-up to get you typing, then moves to one medium-to-hard problem where the follow-ups carry as much weight as the initial query. The interviewer listens as much as they read: whether you volunteer that NULLIF prevents the divide-by-zero before being asked, whether you can say why ROW_NUMBER over RANK, and whether you spot the join fanout early.
The prompt will be deliberately underspecified, the way a product manager would ask it. Scoping it out loud is part of the rubric: what counts as active, what happens on ties, is the grain one row per user or per event. Candidates who ask two sharp scoping questions before typing consistently outperform candidates who start typing immediately, at every level.
Dialect matters less than candidates fear. Postgres is the safest practice target: window functions, CTEs, and aggregation behave identically across Snowflake, BigQuery, Redshift, and MySQL 8 for 85% of patterns. Where a solution here uses a SQLite spelling, the note beside it calls out the Postgres form, and knowing both is itself a signal.
These 20 are the shapes that repeat most in interview reports, drawn from a much larger pool. The full SQL practice catalog has 929 questions from reported data engineer and data scientist interviews, filterable by pattern, difficulty, and company. Every submission runs against a live Postgres 16 process and is replayed across 10 randomized seeds that engineer ties, NULL distributions, and join-key skew, so a query that hardcodes an ID or forgets a tiebreaker fails the way it would in front of an interviewer.
After a passing run, EXPLAIN ANALYZE is one click, with the reference plan side by side, which is the optimization conversation senior loops add. And when problems feel solved, interview mode asks the same questions the way an interviewer does: a vague prompt, a timer, follow-ups, and a verdict.
> We're assembling a service taxonomy from the health-check catalog, bucketing each service on what its name advertises: 'api' in the name makes it an 'api_service', 'cache' or 'redis' makes it a 'cache_service', 'db' or 'postgres' makes it a 'database', and a name matching none of those is 'other'. List each service once alongside the bucket it lands in.
Reading a solution is not the same as writing one
Every engineer who has frozen on a query they had read a dozen times knows the gap. The only preparation that closes it is producing the answer yourself, under time, before the interview does it for you
76% of hiring managers reject on the coding task, not the resume
From HackerRank's 2024 Developer Skills Report. Candidates who look strong on paper still fail the live screen if they haven't done timed, executable practice
5 problem shapes cover 80% of data engineer loops
Dedup, sessionization, top-N-per-group, slowly-changing dimensions, partition tricks. Writing the shapes by hand turns the unfamiliar into pattern recognition
The full 929-question SQL catalog with live Postgres execution, filterable by pattern and company.
ROW_NUMBER, RANK, LAG, LEAD, and frame-clause traps, the tier that decides senior rounds.
INNER versus LEFT, anti-joins, self-joins, and the many-to-many fanout trap.
GROUP BY, HAVING, conditional aggregation, and the AVG-of-CASE NULL bug.
Recursive CTEs, gaps-and-islands, sessionization, and reading EXPLAIN plans.
Window-heavy SQL, funnel and engagement shapes, and the Meta loop structure.
Redshift-flavored SQL, trend questions, and Leadership Principles framing.
Every round in one place: SQL, Python, data modeling, and pipeline design.
The same questions, asked the way an interviewer asks them: vague, timed, with follow-ups.