Loadingone moment…
SQL · Practice Studio
A real SQL workbench on a realistic marketplace warehouse — the exact query patterns data engineering interviews are built on.
Read the chapter, then drill its lab while it's fresh — 10 chapters from SELECT to warehouse cost.
Open the SQL knowledge baseWrite a query that traces returning-user retention by signup cohort and active week.
Inspect paid orders by selecting explicit columns, filtering, and ordering the newest first.
From item-grain rows, count item rows, distinct orders, and distinct products.
Combine a status set, a half-open date range, and an amount threshold with correct precedence.
Label missing countries as 'unknown' and keep only active customers.
Bucket orders into value bands with CASE and return a deterministic ordering.
Group orders by status to sanity-check the table the way an engineer would.
Build a per-role funnel where the rows still in progress have a NULL outcome and must not be dropped.
Report orders, items and revenue per customer from a join that multiplies rows.
Bucket timestamped orders into calendar months without losing the ones near midnight.
Count paid orders when the status column was typed by hand in six different ways.
Stack two order tables into one per-customer total without silently dropping rows.
Filter rows before grouping and groups after it, in one query that needs both.
Find unreturned orders when one return row has a NULL order_id.
Count returns per order where COUNT(*) reports one for orders that have none.
Find orders refunded more than once by grouping and filtering the groups.
Concatenate a name and region into a label that survives a NULL region.
Control where NULLs land in a sort, and break ties so the order is reproducible.
Separate how many lines an order has from how many different products it contains.
Return the order that carries each customer's maximum, not just the maximum value.
Chain customer, order and line item into one readable detail view.
Measure the gap between a timestamp and a date across two tables.
Match a substring in a name, portably and without tripping over quotes.
Bucket refunds into size bands where the order of the CASE arms decides the answer.
Count customers by region and see how GROUP BY treats the ones with no region.
Filter on a computed value when the alias for it does not exist yet.
Join shipped orders to their buyer to attach the buyer's country.
Use a LEFT JOIN and an IS NULL test to find customers with no orders.
Join orders to order_items and count the line items for each order that has any.
Total order revenue grouped by buyer country, labelling a missing country as 'unknown'.
Use a LEFT JOIN and IS NULL to find orders that have no rows in order_items.
Use EXISTS to return customers who have at least one shipped order, without duplicates.
Summarise order count and revenue per status with GROUP BY.
Average order amount per buyer country, labelling a missing country as 'unknown'.
Return buyers whose total revenue is at least 500 using HAVING.
Count the number of different buyers per status.
Per buyer, return paid revenue and refunded amount in one row using conditional aggregation.
Per country, the share of orders that are paid, using decimal-safe division.
Return the second page of a newest-first log, with an ordering that cannot shuffle.
Turn two integer counts into a percentage, on an engine that truncates integer division.
Turn a full name into "A.
Find the indexes nobody reads, and quantify what every write pays to maintain them.
Identify duplicates, negative revenue rows, and broken order-state transitions.
Build a per-genre leaderboard where tied tracks are reported rather than silently dropped.
Accumulate monthly plays within each genre using an explicit window frame.
Dedupe plays to listening days, then measure each listener's longest silence with LAG.
Group each listener's plays into sessions using the gap-flag-cumulative-sum pattern.
Pull both ends of each listener's history, where the default window frame quietly breaks LAST_VALUE.
Put a grand total beside every grouped row with an unpartitioned window.
Average a per-play completion ratio and filter on the group, avoiding integer division.
Compare each month with the one before it, and leave the first month honestly NULL.
Separate a genuine habit from one long sitting by counting days rather than plays.
Split one measure into two tiers in a single pass, without deleting listeners who never played.
Derive each customer's first-ever order, then split every month's business by it.
Build an aggregate the engine does not provide, handling odd and even counts in one query.
Compute a rate that cannot exceed 100% by collapsing the many side before joining.
Annotate every row with a group-level aggregate instead of collapsing the rows away.
Rank products within each customer over a fan-out join, filtered by a status that is typed by hand.
Use named CTE layers to filter paid or shipped orders and aggregate buyer revenue.
Use a scalar subquery to return orders above the overall average amount.
Use a correlated EXISTS subquery to return customers with shipped order activity.
Aggregate buyer revenue in a derived table, then filter the outer query.
Normalize order status buckets once, then count orders by the cleaned status.
Compose a readable multi-CTE country revenue query with clear intermediate names.
Keep the newest snapshot per order_id using ROW_NUMBER.
Rank buyers by total revenue and return the leading buyers.
Use LAG to calculate day gaps between each buyer order and the previous order.
Return daily revenue with cumulative running revenue over time.
Return first and latest order timestamps for each buyer.
Create a compact retention slice by cohort day and activity day offset.
Count events by the day they happened, not the day they arrived.
Assign each user event a stable sequence number using event and ingestion time.
Bucket UTC events into a Pacific-style reporting day using a fixed offset.
Extract device and campaign fields from JSON event payloads.
Expand checkout item arrays into one row per event-item pair.
Count late-arriving events by the original event day.
Write the delta filter for an incremental model so a row that arrived late still gets picked up.
Rename, cast and deduplicate a raw landing table into the one-row-per-order shape the rest of the project can trust.
Read a dbt snapshot as of a past date, where the newest row is the wrong answer.
Report the middle delivery time per city, on an engine with no median function.
Split deliveries into four equal-sized buckets and report the range each one covers.
Fetch the page after a cursor row, so the cost does not grow with how far the user has scrolled.
Turn three months of revenue into three columns with conditional aggregation.
Turn two metric columns back into one row per month and metric, the portable way.
Emulate ROLLUP with UNION ALL, and label the total row so it cannot pass as a status.
Find the customer-months present online and in store, using a set operator rather than a join.
Find the customer-months that happened online but never in store, with EXCEPT rather than an anti-join.
Walk an org chart downwards to any depth with a recursive CTE.
Derive a level for every employee, then count how many sit at each one.
Bound a recursive walk in the recursive term, and know why that is different from filtering the output.
Rank the columns on a table by selectivity and say which ones a B-tree can help.
Rank query shapes by how much they over-read, and name why each one cannot use an index.
Rebuild a multi-stage revenue view while preserving clear intermediate logic.
Total items and refunds for the same orders without each multiplying the other.
Bill each order at the commission rate that applied on the day it was placed.
Restate closing stock on event time and name the pairs whose published figure moved.
Classify a staged extract against its target, where NULL columns break ordinary comparison.
Merge overlapping and contained intervals into sessions before measuring online time.
Rebuild each subscription's state as it stood on a date, from an append-only event log.
Give every conversion to exactly one campaign, or to direct, without inventing credit.
Measure allocation against a 60-second SLA where the worst failures have no event at all.
Report every break between two systems, including references only one of them has.
Compute a trailing-7-day unique audience, which no window function can accumulate.
Price bookings in USD using the most recent published rate, not an exact date match.
Find date ranges on one profile that overlap, where an open-ended range means still running.
Build median and p90 by position when the engine has no percentile function.
Turn a change log into a value for every SKU on every day, carrying the last known figure.
Collapse a landing table to the current version of each event, honouring retractions.
Compare staged customer rows to the current dimension and classify insert, update, unchanged, and delete candidates.
Keep one deterministic staged row per customer key before a target merge.
Return only staged rows that should affect the target, excluding unchanged records.
Find null and duplicate unique keys that would make an upsert unsafe.
Detect changed dimension rows and compute close/open validity boundaries.
Compare current snapshot rows against incoming source state and classify drift.
Identify event partitions that need replay because late or corrected records arrived.
Produce insert, update, delete, and unchanged counts for a load audit report.
Find every order_id stored more than once in order_snapshots, with its count.
Report total customers, the count missing a country, and the missing rate.
Report orders without line items and orphan line items as two counted checks.
Surface orders whose stored total disagrees with the sum of their line items.
Flag orders whose total is more than twice the average order value.
Union four named checks into one pass/fail audit evidence table.
Produce an interview-ready country revenue output with exact columns, ordering, and an unknown bucket.
Join orders to item totals without multiplying order-level revenue.
Keep the latest order snapshot per order with deterministic window logic.
Rank buyers by paid revenue and include tied ranks with stable ordering.
Build a compact cohort retention slice from first order day and later activity days.
Find users whose checkout happened after a view using event-time ordering.
Extract JSON attributes and expand checkout item arrays into item-grain rows.
Classify deduped staged customer rows as insert, update, delete, or unchanged.
Detect current SCD2 rows whose staged attributes require a close/open history change.
Identify event partitions that need replay because of late or corrected records.
Flag expensive, wide, and full-scan query runs from warehouse metadata.
Union multiple named quality checks into one interview-ready audit evidence table.
Show partitions and MB scanned for a date range against the table totals.
Rank the query history by bytes scanned and return the three most expensive.
Find queries that scanned every partition of the table (no pruning).
Sum bytes scanned per analyst and convert it to an estimated dollar cost.
Flag each partition as hot or normal versus the average row count.
Rank queries by bytes scanned per partition to expose wide SELECT-* scans.
Report p50, p75, p90 and p95 delivery times using the nearest-rank definition.
Build the full chain of command as a readable path string, walking down from the root.
Aggregate over each subtree by carrying the root through the recursion.
Collapse each courier's working days into unbroken runs with the date-minus-row-number trick.
Aggregate over the islands to report each courier's longest unbroken stretch.
Apply the leftmost-prefix rule to find single-column indexes a composite already serves.