D8LoopSQL
Session retention breakdown
Write a query that traces returning-user retention by signup cohort and active week.
Practice with the live SQL sandbox.
Open the supported preview exercise with schema context, draft saving, run previews, and structured submit feedback.
Session retention breakdown
Write a query that traces returning-user retention by signup cohort and active week.
Order anomaly audit
Identify duplicates, negative revenue rows, and broken order-state transitions.
Revenue waterfall reconstruction
Rebuild a multi-stage revenue view while preserving clear intermediate logic.
First look at the orders table
Inspect paid orders by selecting explicit columns, filtering, and ordering the newest first.
Count at the right grain
From item-grain rows, count item rows, distinct orders, and distinct products.
Filter January's bigger orders
Combine a status set, a half-open date range, and an amount threshold with correct precedence.
Bucket missing countries
Label missing countries as 'unknown' and keep only active customers.
Label orders by value band
Bucket orders into value bands with CASE and return a deterministic ordering.
Read the status distribution
Group orders by status to sanity-check the table the way an engineer would.
Buyer country on each shipped order
Join shipped orders to their buyer to attach the buyer's country.
Customers who never ordered
Use a LEFT JOIN and an IS NULL test to find customers with no orders.
Line items per order
Join orders to order_items and count the line items for each order that has any.
Revenue by buyer country
Total order revenue grouped by buyer country, labelling a missing country as 'unknown'.
Orders with no line items
Use a LEFT JOIN and IS NULL to find orders that have no rows in order_items.
Customers with a shipped order
Use EXISTS to return customers who have at least one shipped order, without duplicates.
Paid order CTE decomposition
Use named CTE layers to filter paid or shipped orders and aggregate buyer revenue.
Above average order amount
Use a scalar subquery to return orders above the overall average amount.
Customers with shipped orders
Use a correlated EXISTS subquery to return customers with shipped order activity.
Buyer revenue derived table
Aggregate buyer revenue in a derived table, then filter the outer query.
Clean order status CTE
Normalize order status buckets once, then count orders by the cleaned status.
Readable revenue query
Compose a readable multi-CTE country revenue query with clear intermediate names.
Deduplicate order snapshots
Keep the newest snapshot per order_id using ROW_NUMBER.
Top buyers by revenue
Rank buyers by total revenue and return the leading buyers.
Days between buyer orders
Use LAG to calculate day gaps between each buyer order and the previous order.
Running daily revenue
Return daily revenue with cumulative running revenue over time.
First and latest order per buyer
Return first and latest order timestamps for each buyer.
Simple cohort retention slice
Create a compact retention slice by cohort day and activity day offset.
Daily events by event time
Count events by the day they happened, not the day they arrived.
Deterministic event sequence
Assign each user event a stable sequence number using event and ingestion time.
Pacific daily active users
Bucket UTC events into a Pacific-style reporting day using a fixed offset.
Extract device from JSON
Extract device and campaign fields from JSON event payloads.
Expand checkout items
Expand checkout item arrays into one row per event-item pair.
Late arriving event audit
Count late-arriving events by the original event day.
Classify incremental load actions
Compare staged customer rows to the current dimension and classify insert, update, unchanged, and delete candidates.
Dedupe staging before merge
Keep one deterministic staged row per customer key before a target merge.
Build merge source delta
Return only staged rows that should affect the target, excluding unchanged records.
Detect upsert key violations
Find null and duplicate unique keys that would make an upsert unsafe.
SCD2 change boundaries
Detect changed dimension rows and compute close/open validity boundaries.
Current-row snapshot audit
Compare current snapshot rows against incoming source state and classify drift.
Backfill replay window plan
Identify event partitions that need replay because late or corrected records arrived.
Load reconciliation summary
Produce insert, update, delete, and unchanged counts for a load audit report.
Revenue and orders by status
Summarise order count and revenue per status with GROUP BY.
Average order value by country
Average order amount per buyer country, labelling a missing country as 'unknown'.
High-value buyers
Return buyers whose total revenue is at least 500 using HAVING.
Distinct buyers per status
Count the number of different buyers per status.
Paid vs refunded per buyer
Per buyer, return paid revenue and refunded amount in one row using conditional aggregation.
Paid rate by country
Per country, the share of orders that are paid, using decimal-safe division.
Duplicate snapshot keys
Find every order_id stored more than once in order_snapshots, with its count.
Customer null completeness
Report total customers, the count missing a country, and the missing rate.
Referential integrity audit
Report orders without line items and orphan line items as two counted checks.
Reconcile order totals
Surface orders whose stored total disagrees with the sum of their line items.
Flag outlier orders
Flag orders whose total is more than twice the average order value.
Data-quality audit summary
Union four named checks into one pass/fail audit evidence table.
Interview country revenue contract
Produce an interview-ready country revenue output with exact columns, ordering, and an unknown bucket.
Interview fanout-safe order revenue
Join orders to item totals without multiplying order-level revenue.
Interview latest snapshot status
Keep the latest order snapshot per order with deterministic window logic.
Interview top buyers with ties
Rank buyers by paid revenue and include tied ranks with stable ordering.
Interview cohort retention slice
Build a compact cohort retention slice from first order day and later activity days.
Interview event funnel sequence
Find users whose checkout happened after a view using event-time ordering.
Interview JSON array expansion
Extract JSON attributes and expand checkout item arrays into item-grain rows.
Interview incremental action plan
Classify deduped staged customer rows as insert, update, delete, or unchanged.
Interview SCD2 current-row check
Detect current SCD2 rows whose staged attributes require a close/open history change.
Interview late-data replay check
Identify event partitions that need replay because of late or corrected records.
Interview query-cost triage
Flag expensive, wide, and full-scan query runs from warehouse metadata.
Interview audit union capstone
Union multiple named quality checks into one interview-ready audit evidence table.
Partition pruning scan
Show partitions and MB scanned for a date range against the table totals.
Most expensive queries
Rank the query history by bytes scanned and return the three most expensive.
Full-scan detector
Find queries that scanned every partition of the table (no pruning).
Scan cost by analyst
Sum bytes scanned per analyst and convert it to an estimated dollar cost.
Hot partition skew
Flag each partition as hot or normal versus the average row count.
Wide-scan efficiency
Rank queries by bytes scanned per partition to expose wide SELECT-* scans.