Practice. Ship. Repeat.
Hand-picked problems across 6 languages — banking, e-commerce, analytics, system design. Run them right in your browser; ask AI when stuck.
Employee With Manager Name
Self-join `employees(id, name, manager_id)` to return each employee with their manager's name. CEOs (NULL manager_id) should appear with manager 'N/A'.
Calendar × Stores Heatmap
Given `dates(d)` (a 7-day calendar) and `stores(id)` (3 stores), CROSS JOIN them and LEFT JOIN to `sales(d, store_id, total)` so missing days show 0. Output (d, store_id, total).
Nth Highest Salary Per Dept
From `employees(dept_id, name, salary)` return the **2nd highest** salaried employee in each dept (NULL if dept has only one employee).
Day-Over-Day Sales % Change
Given `daily_sales(d, total)` (continuous days), output (d, total, prev_total, pct_change). pct_change = NULL on day 1.
Bucket Users Into Percentile Quartiles
Given `users(id, ltv)`, label each user 'Q1'..'Q4' based on LTV quartiles. Q1 = highest 25%, Q4 = lowest 25%. Output (id, ltv, bucket).
Median Salary Per Department
From `employees(dept_id, salary)`, compute the **median** salary per dept. Output (dept_id, median_salary). Even-count depts: average the two middle values.
Why `LIKE '%foo'` Can't Use an Index
Why does `WHERE email LIKE '%@gmail.com'` ignore the index on `email` even when one exists? Propose two practical mitigations.
Choosing Composite Index Column Order
A frequent query is `WHERE country = ? AND city = ? AND signup_d >= ?`. Should you create the index as `(country, city, signup_d)` or `(signup_d, country, city)`? Explain.
Functions On Indexed Columns Kill Indexes
`WHERE LOWER(email) = 'a@x.com'` ignores the index on `email`. Explain why, and propose two fixes.
Orders With Product Details
Given `orders(id, product_id, qty)` and `products(id, name, price)`, return each order id with `product_name` and `total = qty * price`, sorted by total desc.
Mutual Friends Count
Table `friendships(user_a, user_b)` stores undirected friendships once (a < b). Return pairs (u,v) and the count of mutual friends. Output only pairs with ≥1 mutual friend, sorted by count desc.
3-Table Revenue Per Region
Join `regions(id,name)`, `customers(id, region_id)`, `orders(id, customer_id, amount)` to produce total revenue per region. Include regions with zero revenue (output 0).
Rank Students By Score
Given `scores(student, score)`, output (student, score, rnk) using `RANK()` ordered by score desc. Ties share a rank; the next rank skips.
Running Total Per User
Given `txns(user_id, ts, amount)`, output (user_id, ts, amount, running_total) where running_total is the cumulative sum **per user** ordered by ts.
Spotting & Fixing N+1 Queries
An ORM is issuing 1 query for the orders list and then 1 query *per order* to fetch the customer name (1+N). Identify the anti-pattern and rewrite it as a single SQL.
When `OR` Disables Index — Rewrite With UNION
`SELECT * FROM users WHERE email = ? OR phone = ?` may table-scan even when both columns are indexed. Why, and how does UNION ALL fix it?
Products With Recent Sales
List products that had at least one sale in the **last 30 days**. Return `id, name` ordered by name. Use a SEMI-JOIN pattern (EXISTS or IN with subquery).
Full-Outer Reconcile
You have `legacy_users(id, email)` and `new_users(id, email)`. Find every record that exists in only one table OR has the same id but different email — sorted by id. SQLite has no FULL OUTER JOIN — emulate it.
First Purchase Per User
Given `purchases(user_id, ts, item)`, output one row per user with their **first-ever** item and ts. Use a window function (FIRST_VALUE / ROW_NUMBER), not GROUP BY.
Cumulative Distinct Users
Given `events(d, user_id)`, output (d, cumulative_distinct_users) — the count of distinct users who have appeared on or before day d.
Read EXPLAIN — Index vs Full Scan
Given table `users(id PRIMARY KEY, email)` (10M rows), explain why `SELECT * FROM users WHERE id = 42` runs in microseconds while `SELECT * FROM users WHERE email = 'x@y.com'` is slow. What single change fixes the slow query?
Batch Deletes To Avoid Locks
Deleting 50M rows in a single statement holds a lock and bloats the WAL. Write a batched-delete loop that processes 50K rows at a time. (Pseudo-SQL acceptable.)
Denormalise A Hot Counter
`SELECT COUNT(*) FROM likes WHERE post_id = ?` is now hot and slow at 100M rows. The post page hits it on every render. Propose a denormalisation strategy and write the trigger / atomic SQL that maintains it.
Customers Who Placed Orders
Given `customers(id, name)` and `orders(id, customer_id, amount)`, return the **distinct names** of customers who placed at least one order, sorted alphabetically.