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.
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.
Customers Without Orders
Find every customer who has **never** placed an order. Return `id, name` ordered by `id`.
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'.
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).
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).
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.
Employees Earning More Than Their Manager
From `employees(id, name, salary, manager_id)`, return the names of employees whose salary is strictly greater than their direct manager's salary. Sorted by name.
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.
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.
7-Day Moving Average
Given `metrics(d, value)`, compute a 7-day trailing moving average column (`avg7`). For days with fewer than 7 history points, use what's available.
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).
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.
Longest Consecutive-Day Streak
Given `logins(user_id, login_date)` (one row per user/day), return each user's longest streak of consecutive days logged in. Output (user_id, longest_streak).
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.
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.
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?
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.
Covering Index For SELECT-Only Columns
Query: `SELECT email FROM users WHERE country = 'IN' AND city = 'Pune'`. The current index is `(country, city)`. Why is the planner still doing extra heap fetches, and what is a 'covering index'?