930 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. We have 930 of these 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 set of 930 SQL interview questions continues with the same live validation.
Computed from the 930 SQL questions by concept on this site, across the 173 employers they were reported from. Percentages are the share of those problems using each concept, so they overlap: a single question usually pulls in 3 or 4 at once.
| Concept | Share of problems | Employers | What comes up |
|---|---|---|---|
| Aggregation (GROUP BY, HAVING) | 65% (600) | 139 | Filtering groups after aggregation vs rows before it — the WHERE vs HAVING distinction. |
| Subqueries and EXISTS | 23% (213) | 101 | 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% (194) | 78 | INNER vs LEFT, self-joins, and the many-to-many fanout that silently doubles every downstream metric. |
| Deduplication and DISTINCT | 18% (171) | 72 | 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) | 70 | DATE_TRUNC bucketing, date arithmetic, and DATE vs TIMESTAMP boundary bugs. |
| Conditional aggregation (CASE WHEN) | 14% (133) | 59 | 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. |
These counts come from the same practice problems you can solve here, and they update as new questions come in.
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 and the earlier impression ahead whenever two tie on revenue.
SELECT impression_time, revenue FROM ad_impressions ORDER BY revenue DESC LIMIT 3
On-call wants every error it has actually measured tagged with a severity label based on how often that error 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. An error whose fire count was never recorded can't be sized, so leave it out, 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 TO_CHAR(bill_date, 'YYYY-MM') 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 TO_CHAR(bill_date, 'YYYY-MM')
ORDER BY month
The security team is auditing which owners held a live API token on November 1 of 2026; list each qualifying owner once, walking from the lowest owner id up to the highest. 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 DATEDIFF('day', started, next_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. Lay the audit out as an alphabetical register by flag name and then by owner, with the oldest flags first where those match and any flag already switched off listed ahead of one still running.
SELECT flag_name, owner,
CASE WHEN enabled = 1 OR updated IS NULL THEN 'Yes' ELSE 'No' END AS still_enabled,
CAST(DATEDIFF('day', created, '2026-05-01') / 365 AS INTEGER) AS years_since_creation
FROM feat_flags
WHERE DATEDIFF('day', created, '2026-05-01') > 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 stand up the three most reliable regions, most reliable first, 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. Walk the flagged charges from the oldest to the most recent, so the team clears the backlog in the order the charges actually hit customers.
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 DATEDIFF('day', prev_date, transaction_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. Walking forward from the earliest month, total each month's transaction revenue and show how it moved, in percent, from the immediately preceding month.
WITH monthly AS (
SELECT TO_CHAR(transaction_date, 'YYYY-MM') AS month,
SUM(total_amount) AS revenue
FROM transactions
GROUP BY TO_CHAR(transaction_date, 'YYYY-MM')
),
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 runs a first-touch model: every conversion is credited to the very first ad impression a user ever saw, whether or not they clicked, and a user counts as converted the moment they show up in the transactions table. For each converted user return their user_id, the ad_campaign of that earliest impression, and its impression_time, walking from the lowest user_id up to the highest.
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. Each row in cloud_costs is a single cloud charge, and credits post as a negative amount, so total each month's real spend from the positive charges only and treat the prior month's total as the current month's forecast. For every month that has a prior month to compare against, report its year-month, its actual cost, the forecast, the absolute percent error of the forecast against that month's actual cost, and whether the actual came in over or under the forecast.
WITH monthly AS ( SELECT TO_CHAR(bill_date, 'YYYY-MM') AS ym, SUM(amount) AS total_amount FROM cloud_costs WHERE amount IS NOT NULL AND amount > 0 GROUP BY TO_CHAR(bill_date, 'YYYY-MM') ), 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
We run batch jobs on a shared pool of workers, where a job occupies one worker from its start timestamp until its end timestamp. Find the smallest pool that could have run every completed job without any job ever waiting, counting a job that was logged across several rows only once.
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
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) 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,
DATEDIFF('day',
LAG(event_timestamp) OVER (
PARTITION BY event_type
ORDER BY event_timestamp
),
event_timestamp
) * 24 AS hours_diff
FROM event_data
) gaps
WHERE hours_diff IS NOT NULL
GROUP BY event_type
A FinOps team is auditing cloud costs and only trusts charges tied to a known account, setting unattributed line items aside. Working from each billing period's total of the remaining charges, find every stretch where the total climbed for at least two periods running, and return where each stretch began and how many consecutive rises it held, earliest first.
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
)Definitional questions from the first 10 minutes of a phone screen. Answering these fast buys clock for the queries that decide the round.
SQL is the declarative language for defining and querying relational data. Its statements group into 4 families: DDL defines structure (CREATE, ALTER, DROP), DML works on rows (SELECT, INSERT, UPDATE, DELETE), DCL manages access (GRANT, REVOKE), and TCL controls transactions (COMMIT, ROLLBACK, SAVEPOINT). Interviewers open with this to settle vocabulary before the querying starts.
INNER keeps only rows that match on the join condition. LEFT keeps every left row and fills the right side with NULLs where nothing matched; RIGHT mirrors it; FULL keeps unmatched rows from both sides. In practice: reach for LEFT JOIN whenever the right side can legitimately be missing, and rewrite any RIGHT JOIN as a LEFT JOIN with the tables swapped, because that is how the next reader will parse it.
A primary key identifies a row: unique, non-NULL, one per table. A foreign key is a column referencing another table's primary key, which is how the schema encodes relationships. The data engineering wrinkle: warehouses like Snowflake and BigQuery accept foreign key declarations but do not enforce them, so the constraint documents join intent while enforcement moves into pipeline checks.
A condition in ON is applied while matching, so unmatched left rows survive with NULLs. The same condition in WHERE runs after the join and filters those NULL rows out, silently converting the LEFT JOIN into an INNER one. Any predicate on the nullable side of a LEFT JOIN belongs in ON, or must explicitly allow NULL through in WHERE.
Under three-valued logic, any comparison with NULL evaluates to unknown, so col = NULL never matches and even NULL = NULL is not true. Checks require IS NULL and IS NOT NULL. Aggregates skip NULLs, and a WHERE predicate drops rows whose comparison is unknown, which is how NULLs fall out of filtered counts without any error being raised.
COALESCE returns the first non-NULL argument, the standard way to supply a default. NULLIF(a, b) returns NULL when a equals b, and its everyday use is NULLIF(denominator, 0) to turn a divide-by-zero into a NULL. Composed together they express most defensive-NULL logic without a CASE.
DISTINCT removes duplicate rows from the result, paying a sort or hash over the whole set. Two flags for the interview: a DISTINCT that fixes surprise duplicates is usually masking join fanout upstream, and Postgres offers DISTINCT ON for keep-1-row-per-key, though ROW_NUMBER is the portable spelling.
Grouping columns and aggregates of everything else. A bare non-grouped column is ambiguous, several rows feed each group, and engines either reject it (Postgres) or historically picked an arbitrary value (older MySQL). State the output grain first, 1 row per what, and the SELECT list follows from it.
CHAR(n) pads every value to a fixed n characters; VARCHAR(n) stores the actual length up to n. Fixed padding wastes space and complicates comparisons, so VARCHAR is the default choice in practice. In Postgres, TEXT and VARCHAR perform identically, and the length cap is a data-quality constraint rather than an optimization.
A view is a stored query that runs at read time: no data of its own, always current, and a clean place to centralize business logic or narrow access to specific columns. In warehouse practice views define the semantic layer, and when one gets expensive enough to hurt, the follow-up conversation is materializing it, covered below.
A B-tree index is a sorted side structure that turns a scan into a seek for selective predicates. Every write then maintains it, so indexes tax INSERT and UPDATE throughput, and a low-cardinality column like status rarely earns one. Columnar warehouses mostly skip B-trees entirely in favor of clustering and sort keys, a difference interviewers expect data engineers to know.
NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT. OLTP databases enforce all of them; analytical warehouses enforce almost none, which moves the same guarantees into pipeline validation such as dbt tests. Interviewers usually follow up by asking which layer owns each guarantee.
Scalar (1 value, usable anywhere an expression goes), row or list (feeding IN and EXISTS predicates), and table (a derived table in FROM, which needs an alias). Correlated subqueries reference the outer row and re-evaluate per row, with the rewrite tradeoffs covered in the next section.
CASE is an expression, so it returns a value inside SELECT, WHERE, or ORDER BY rather than branching control flow. The searched form (CASE WHEN cond THEN x) evaluates top to bottom and stops at the first hit; with no ELSE, non-matching rows get NULL, the detail behind the AVG bug in the mistakes section below.
Joining a table to itself under 2 aliases: employees to their managers, an event to the previous event, a row to its parent. Sequential-comparison self joins have largely been replaced by LAG and LEAD, so mention the window alternative and keep the self join for hierarchy walks.
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 useful trick 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. The materialization tradeoff is the part interviewers want to hear named.
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 1 day instead of 5 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 2 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 payoff is time: a warm-up that costs no clock leaves the whole round for the question that 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 real submissions to these questions, 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-dataset 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. Window functions, CTEs, and aggregation behave the same across the warehouses you will actually interview against for the overwhelming majority of patterns, and the solutions here stick to that portable core. What interviewers grade is whether the shape of the query is right; naming a spelling difference out loud when one exists 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 problems page has 930 questions from reported data engineer and data scientist interviews, filterable by pattern, difficulty, and company. Every submission executes live 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.
> The retention team needs repeat-buyer signals. Find users who made a second transaction within 1 to 7 days of a previous one, excluding same-day purchases. Return each qualifying user ID once, ordered from the lowest ID up to the highest.
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
All 930 SQL questions with live 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.
1 data engineering challenge a week on dirty production-shaped data, scored blind on a hidden dataset. Submit before the freeze; results at the reveal.
50+ guides covering every round, company, and role