Session 08 · Phase 1: Foundations & SQL

SQL Practice Marathon — 30 Mixed Problems

Time to consolidate. Thirty problems spanning every topic from Sessions 02–07 — try each one yourself before opening the solution.

⏱ ~3 hrs 🧠 Practice 🎯 Highest priority

How to Use This Marathon

  1. Predict before you run. Write your query, then write down the expected result before executing it.
  2. Time yourself. Easy = 1–2 min, Medium = 3–5 min, Hard = 5–8 min.
  3. Narrate out loud. This is exactly what you'll do in a live coding interview.
  4. Reveal and compare. If your answer differs, figure out why — not just the correct syntax.
💡
The live-coding framework: (1) restate the business question, (2) name the table/columns, (3) write the query, (4) explain what the output means. Interviewers score the thinking, not just the syntax.

Schema Reference

TableColumns
customerscustomer_id, customer_name, city, account_type, balance, join_date
transactionstxn_id, customer_id, amount, txn_date
employeesemp_id, emp_name, manager_id, dept

Sample data (same as Sessions 02–07): 7 customers, 8 transactions, 5 employees.

Part A — SELECT & Filtering (Problems 1–5)

1. List the names of all customers who live in Mumbai. Easy 
SELECT customer_name
FROM customers
WHERE city = 'Mumbai';
2. List customers with a balance greater than 50,000. Easy 
SELECT customer_name, balance
FROM customers
WHERE balance > 50000;
3. List customers in Mumbai or Delhi who also have a balance over 20,000. Medium 
SELECT customer_name, city, balance
FROM customers
WHERE (city = 'Mumbai' OR city = 'Delhi')
  AND balance > 20000;
4. List customers whose name starts with the letter 'A'. Easy 
SELECT customer_name
FROM customers
WHERE customer_name LIKE 'A%';
5. List customers who joined in 2023 or later, ordered by balance (highest first). Medium 
SELECT customer_name, join_date, balance
FROM customers
WHERE join_date >= '2023-01-01'
ORDER BY balance DESC;

Part B — String, Date & Numeric Functions (Problems 6–10)

6. Show all customer names in uppercase. Easy 
SELECT UPPER(customer_name) AS name_upper
FROM customers;
7. Show a single column combining name and city, e.g. "Aarav Sharma - Mumbai". Easy 
SELECT CONCAT(customer_name, ' - ', city) AS customer_location
FROM customers;
8. List each customer and the length of their name. Easy 
SELECT customer_name, LENGTH(customer_name) AS name_length
FROM customers;
9. Add a column classifying each balance as High (≥100000), Medium (≥30000), or Low. Medium 
SELECT customer_name, balance,
       CASE
           WHEN balance >= 100000 THEN 'High'
           WHEN balance >= 30000  THEN 'Medium'
           ELSE 'Low'
       END AS balance_category
FROM customers;
10. Show each customer's join year and join month. Easy 
-- MySQL style
SELECT customer_name, YEAR(join_date) AS yr, MONTH(join_date) AS mth
FROM customers;

Part C — Aggregations, GROUP BY & HAVING (Problems 11–15)

11. Count the total number of customers. Easy 
SELECT COUNT(*) AS total_customers
FROM customers;
12. Count the number of distinct cities. Easy 
SELECT COUNT(DISTINCT city) AS unique_cities
FROM customers;
13. Find the total balance for each account type. Medium 
SELECT account_type, SUM(balance) AS total_balance
FROM customers
GROUP BY account_type;
14. Find the average balance per city, rounded to 0 decimals. Medium 
SELECT city, ROUND(AVG(balance), 0) AS avg_balance
FROM customers
GROUP BY city;
15. List cities that have more than one customer. Medium 
SELECT city, COUNT(*) AS customer_count
FROM customers
GROUP BY city
HAVING COUNT(*) > 1;

Part D — Joins (Problems 16–20)

16. List customer names with their transaction amounts (only customers with transactions). Medium 
SELECT c.customer_name, t.amount
FROM customers c
INNER JOIN transactions t ON c.customer_id = t.customer_id;
17. List ALL customers with their transaction amounts (NULL where none). Medium 
SELECT c.customer_name, t.amount
FROM customers c
LEFT JOIN transactions t ON c.customer_id = t.customer_id;
18. Find customers who have never made a transaction. Hard 
SELECT c.customer_name
FROM customers c
LEFT JOIN transactions t ON c.customer_id = t.customer_id
WHERE t.txn_id IS NULL;
19. Find the total spend per customer (including 0 for none). Medium 
SELECT c.customer_name,
       COALESCE(SUM(t.amount), 0) AS total_spent
FROM customers c
LEFT JOIN transactions t ON c.customer_id = t.customer_id
GROUP BY c.customer_name
ORDER BY total_spent DESC;
20. List each employee with their manager's name (self join). Hard 
SELECT e.emp_name AS employee, m.emp_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

Part E — Subqueries & CTEs (Problems 21–25)

21. List customers whose balance is above the overall average (subquery). Medium 
SELECT customer_name, balance
FROM customers
WHERE balance > (SELECT AVG(balance) FROM customers);
22. Find the second-highest balance (subquery). Hard 
SELECT MAX(balance) AS second_highest
FROM customers
WHERE balance < (SELECT MAX(balance) FROM customers);
23. Show each customer with their transaction count (correlated subquery). Hard 
SELECT c.customer_name,
       (SELECT COUNT(*) FROM transactions t
        WHERE t.customer_id = c.customer_id) AS txn_count
FROM customers c;
24. List customers with more than one transaction (CTE). Medium 
WITH txn_counts AS (
    SELECT customer_id, COUNT(*) AS no_of_txn
    FROM transactions GROUP BY customer_id
)
SELECT c.customer_name
FROM customers c
JOIN txn_counts tc ON c.customer_id = tc.customer_id
WHERE tc.no_of_txn > 1;
25. Find the average number of transactions per customer (CTE). Hard 
WITH txn_counts AS (
    SELECT customer_id, COUNT(*) AS no_of_txn
    FROM transactions GROUP BY customer_id
)
SELECT AVG(no_of_txn) AS avg_txn_per_customer
FROM txn_counts;

Part F — Window Functions (Problems 26–30)

26. Rank customers by balance (highest first) with ROW_NUMBER, RANK, and DENSE_RANK. Medium 
SELECT customer_name, balance,
       ROW_NUMBER() OVER (ORDER BY balance DESC) AS rn,
       RANK()       OVER (ORDER BY balance DESC) AS rk,
       DENSE_RANK() OVER (ORDER BY balance DESC) AS dr
FROM customers;
27. Find the top customer by balance in each city (top-N per group). Hard 
SELECT city, customer_name, balance
FROM (
    SELECT city, customer_name, balance,
           ROW_NUMBER() OVER (PARTITION BY city ORDER BY balance DESC) AS rn
    FROM customers
) t
WHERE rn = 1;
28. Compute a running total of transaction amounts ordered by date. Hard 
SELECT txn_date, amount,
       SUM(amount) OVER (ORDER BY txn_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM transactions
ORDER BY txn_date;
29. Show each transaction amount along with the previous transaction's amount (LAG). Hard 
SELECT txn_id, amount,
       LAG(amount) OVER (ORDER BY txn_date) AS prev_amount
FROM transactions
ORDER BY txn_date;
30. Show each customer's balance as a percentage of their city's total. Hard 
SELECT customer_name, city, balance,
       ROUND(100.0 * balance / SUM(balance) OVER (PARTITION BY city), 1) AS pct_of_city
FROM customers;
📋 Stable content — Reviewed: August 2026

Interview Questions — The Live-Coding Classics

These are the SQL problems interviewers ask again and again. Each maps to problems you've already solved above — the value here is recognizing the pattern instantly.

IQ1. How do you approach a SQL live-coding question in an interview?

Model answer: "I don't jump to code. I restate the business question, name the table and columns I need, mention any edge case, then write the query and explain the output. Showing my reasoning matters more than a silent correct query."

IQ2. Find the second-highest salary.

Approach: max below the max, or DENSE_RANK = 2. See Problem 22 and the DENSE_RANK approach.

IQ3. How do you find duplicate rows in a table?

Approach: GROUP BY the identifying column + HAVING COUNT(*) > 1 (see Problem 15).

IQ4. Find the top N items per group.

Approach: ROW_NUMBER() OVER (PARTITION BY … ORDER BY …) wrapped in a subquery, filter rn <= N (see Problem 27).

IQ5. Compute a running total / cumulative sum.

Approach: SUM(…) OVER (ORDER BY … ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) (see Problem 28).

IQ6. Find customers with no orders.

Approach: LEFT JOIN … WHERE right.key IS NULL — the anti-join (see Problem 18).

IQ7. Compute month-over-month growth.

Approach: aggregate by month, then LAG(revenue) OVER (ORDER BY month) (see Problem 29 pattern).

IQ8. Compute a ratio or percent-of-total.

Approach: divide the row value by SUM(…) OVER (PARTITION BY …) (see Problem 30).

💡
Pattern recognition beats memorization. Nearly every SQL interview problem is one of eight patterns: filter, aggregate, join, anti-join, subquery, CTE, top-N per group, or running total. Learn to spot the pattern from the question, and the query writes itself.

Key Takeaways

1

Predict the output before running — that's the skill interviewers test.

2

Almost every problem is one of 8 patterns: filter, aggregate, join, anti-join, subquery, CTE, top-N, running total.

3

Narrate your thinking out loud — the framework scores more than raw syntax.

4

If stuck, break the problem into steps and consider a CTE.

5

Time-box yourself: easy ≤2 min, medium ≤5 min, hard ≤8 min.

Objective Questions — Test Your Understanding

Q1. Which clause filters rows before grouping/aggregation?

Q2. Which join returns only the rows that match in both tables?

Q3. Which function returns the nth-highest distinct value cleanly (no skipped ranks)?

Q4. To find duplicate values in a column, you combine GROUP BY with…

Q5. The "top N per group" pattern uses which combination?