SQL Practice Set for a 3–5 Year Developer¶

If you have 3–5 years of experience, you should already be comfortable with:

joins
aggregations
subqueries
CTEs
window functions
indexing concepts
query optimization basics
transactional thinking

If you struggle with more than half of these without Googling syntax every 2 minutes, your SQL depth is weaker than your experience level suggests.

Below are 25 interview-grade SQL problems that separate “CRUD developers” from engineers who actually understand data systems.

Sample Schema¶

Assume these tables exist:

Customers(
    customer_id INT,
    name VARCHAR(100),
    city VARCHAR(100),
    signup_date DATE
)

Orders(
    order_id INT,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10,2),
    status VARCHAR(20)
)

Employees(
    emp_id INT,
    emp_name VARCHAR(100),
    department_id INT,
    salary DECIMAL(10,2),
    manager_id INT,
    joining_date DATE
)

Departments(
    department_id INT,
    department_name VARCHAR(100)
)

Products(
    product_id INT,
    product_name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10,2)
)

Order_Items(
    order_item_id INT,
    order_id INT,
    product_id INT,
    quantity INT
)

1. Find Top 5 Customers by Total Purchase¶

SELECT
    c.customer_id,
    c.name,
    SUM(o.amount) AS total_purchase
FROM Customers c
JOIN Orders o
    ON c.customer_id = o.customer_id
WHERE o.status = 'Completed'
GROUP BY c.customer_id, c.name
ORDER BY total_purchase DESC
LIMIT 5;

2. Find Employees Earning More Than Their Manager¶

SELECT
    e.emp_name AS employee,
    m.emp_name AS manager,
    e.salary,
    m.salary AS manager_salary
FROM Employees e
JOIN Employees m
    ON e.manager_id = m.emp_id
WHERE e.salary > m.salary;

3. Find Duplicate Customers Based on Name and City¶

SELECT
    name,
    city,
    COUNT(*) AS duplicate_count
FROM Customers
GROUP BY name, city
HAVING COUNT(*) > 1;

4. Get Second Highest Salary¶

SELECT MAX(salary) AS second_highest_salary
FROM Employees
WHERE salary < (
    SELECT MAX(salary)
    FROM Employees
);

5. Find Department-wise Highest Paid Employee¶

SELECT *
FROM (
    SELECT
        e.emp_name,
        d.department_name,
        e.salary,
        RANK() OVER (
            PARTITION BY e.department_id
            ORDER BY e.salary DESC
        ) AS rnk
    FROM Employees e
    JOIN Departments d
        ON e.department_id = d.department_id
) t
WHERE rnk = 1;

6. Find Customers Who Never Ordered¶

SELECT
    c.customer_id,
    c.name
FROM Customers c
LEFT JOIN Orders o
    ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

7. Monthly Revenue Report¶

SELECT
    TO_CHAR(order_date, 'YYYY-MM') AS month,
    SUM(amount) AS revenue
FROM Orders
GROUP BY TO_CHAR(order_date, 'YYYY-MM')
ORDER BY month;

8. Find Running Total of Orders¶

SELECT
    order_id,
    order_date,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date
    ) AS running_total
FROM Orders;

9. Find Customers with More Than 3 Orders¶

SELECT
    customer_id,
    COUNT(*) AS total_orders
FROM Orders
GROUP BY customer_id
HAVING COUNT(*) > 3;

10. Find Products Never Ordered¶

SELECT
    p.product_id,
    p.product_name
FROM Products p
LEFT JOIN Order_Items oi
    ON p.product_id = oi.product_id
WHERE oi.product_id IS NULL;

11. Find Order with Maximum Amount Per Customer¶

SELECT *
FROM (
    SELECT
        order_id,
        customer_id,
        amount,
        RANK() OVER (
            PARTITION BY customer_id
            ORDER BY amount DESC
        ) AS rnk
    FROM Orders
) t
WHERE rnk = 1;

12. Find Employees Joined in Last 6 Months¶

SELECT *
FROM Employees
WHERE joining_date >= CURRENT_DATE - INTERVAL '6 months';

13. Count Orders by Status¶

SELECT
    status,
    COUNT(*) AS total_orders
FROM Orders
GROUP BY status;

14. Find Average Salary Department-wise¶

SELECT
    d.department_name,
    AVG(e.salary) AS avg_salary
FROM Employees e
JOIN Departments d
    ON e.department_id = d.department_id
GROUP BY d.department_name;

Assume:

User_Logins(
    user_id INT,
    login_date DATE
)

SELECT
    user_id,
    login_date,
    login_date - (ROW_NUMBER() OVER (
        PARTITION BY user_id
        ORDER BY login_date
    )::int * INTERVAL '1 day') AS grp
FROM User_Logins;

This is a classic gaps-and-islands problem. If you don't know this pattern, learn it.

16. Find Percentage Contribution of Each Product¶

SELECT
    p.product_name,
    SUM(oi.quantity * p.price) AS revenue,
    ROUND(
        100 * SUM(oi.quantity * p.price) /
        SUM(SUM(oi.quantity * p.price)) OVER (),
        2
    ) AS contribution_pct
FROM Products p
JOIN Order_Items oi
    ON p.product_id = oi.product_id
GROUP BY p.product_name;

17. Delete Duplicate Rows¶

DELETE FROM Customers c1
USING Customers c2
WHERE c1.name = c2.name
  AND c1.city = c2.city
  AND c1.customer_id > c2.customer_id;

18. Find Nth Highest Salary¶

SELECT DISTINCT salary
FROM Employees e1
WHERE 3 = (
    SELECT COUNT(DISTINCT salary)
    FROM Employees e2
    WHERE e2.salary >= e1.salary
);

19. Pivot Order Status Counts¶

SELECT
    customer_id,
    SUM(CASE WHEN status = 'Completed' THEN 1 ELSE 0 END) completed_orders,
    SUM(CASE WHEN status = 'Pending' THEN 1 ELSE 0 END) pending_orders,
    SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) cancelled_orders
FROM Orders
GROUP BY customer_id;

20. Find Latest Order per Customer¶

SELECT *
FROM (
    SELECT
        order_id,
        customer_id,
        order_date,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY order_date DESC
        ) AS rn
    FROM Orders
) t
WHERE rn = 1;

21. Find Customers with Orders on Consecutive Days¶

SELECT DISTINCT o1.customer_id
FROM Orders o1
JOIN Orders o2
    ON o1.customer_id = o2.customer_id
    AND (o2.order_date - o1.order_date) = 1;

22. Calculate Employee Salary Rank Globally¶

SELECT
    emp_name,
    salary,
    DENSE_RANK() OVER (
        ORDER BY salary DESC
    ) AS salary_rank
FROM Employees;

23. Find Median Salary¶

WITH Ranked AS (
    SELECT
        salary,
        ROW_NUMBER() OVER (ORDER BY salary) AS rn,
        COUNT(*) OVER () AS total_count
    FROM Employees
)
SELECT AVG(salary) AS median_salary
FROM Ranked
WHERE rn IN (
    FLOOR((total_count + 1) / 2),
    FLOOR((total_count + 2) / 2)
);

24. Detect Missing IDs¶

SELECT t1.order_id + 1 AS missing_id
FROM Orders t1
LEFT JOIN Orders t2
    ON t1.order_id + 1 = t2.order_id
WHERE t2.order_id IS NULL;

25. Find Customers Whose Spending Increased Every Month¶

This is not trivial. It tests analytical thinking.

WITH monthly_spend AS (
    SELECT
        customer_id,
        TO_CHAR(order_date, 'YYYY-MM') AS month,
        SUM(amount) AS total_spend
    FROM Orders
    GROUP BY customer_id, TO_CHAR(order_date, 'YYYY-MM')
),
ranked AS (
    SELECT
        customer_id,
        month,
        total_spend,
        LAG(total_spend) OVER (
            PARTITION BY customer_id
            ORDER BY month
        ) AS prev_spend
    FROM monthly_spend
)
SELECT DISTINCT customer_id
FROM ranked
WHERE total_spend > prev_spend;

SQL Optimization Scenarios + EXISTS vs IN vs JOIN¶

Most developers claim they “know SQL optimization” because they added an index once and query time dropped.

That’s not optimization. That’s accidental success.

Real SQL optimization means:

understanding execution plans
minimizing scanned rows
reducing sort/hash operations
controlling join cardinality
avoiding unnecessary materialization
designing queries for scale, not sample data

Below are practical scenarios you should be able to reason through at a 3–5 year level.

PART 1 — SQL Optimization Scenarios¶

Scenario 1 — Query Suddenly Becomes Slow¶

Problem¶

```sql id="e0rv98" SELECT * FROM Orders WHERE customer_id = 1001;

Used to run in 50ms.
Now takes 8 seconds.

---

## What weak developers do

* Restart DB
* Increase server size
* Add random indexes
* Blame network

---

## What good engineers investigate

### 1. Check Execution Plan

```sql id="qvq4jg"
EXPLAIN ANALYZE
SELECT *
FROM Orders
WHERE customer_id = 1001;

Look for:

Full Table Scan
Index Scan
Rows examined
Cost
Hash Join
Temporary tables
Filesort

2. Check Missing Index¶

```sql id="qmgjlwm" CREATE INDEX idx_orders_customer ON Orders(customer_id);

---

### 3. Check Selectivity

If:

* 95% rows have same customer_id
* index becomes useless

Indexes help when filtering is selective.

---

# Scenario 2 — Composite Index Order Mistake

## Query

```sql id="bl9hmv"
SELECT *
FROM Orders
WHERE customer_id = 101
AND order_date = '2026-01-10';

Developer creates:

```sql id="m7p81p" CREATE INDEX idx_wrong ON Orders(order_date, customer_id);

---

## Problem

Index order matters.

Correct index depends on:

* filtering pattern
* cardinality
* leading column usage

Better:

```sql id="hjlwmn"
CREATE INDEX idx_correct
ON Orders(customer_id, order_date);

Rule¶

Composite indexes work left-to-right.

Index:

```sql id="54ew3y" (a, b, c)

Works for:

* a
* a,b
* a,b,c

Not efficiently for:

* b,c
* c only

---

# Scenario 3 — SELECT * Disaster

## Bad

```sql id="0g6k9k"
SELECT *
FROM Orders;

On a 200-column table.

Why it’s bad¶

unnecessary IO
network overhead
memory usage
prevents covering indexes

Better¶

```sql id="uwf4xy" SELECT order_id, customer_id, amount FROM Orders;

---

# Scenario 4 — Function on Indexed Column

## Bad

```sql id="8wby9r"
SELECT *
FROM Orders
WHERE EXTRACT(YEAR FROM order_date) = 2025;

Why bad¶

Function prevents index usage.

DB must compute YEAR() for every row.

Better¶

```sql id="g1t12q" SELECT * FROM Orders WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01';

This is called a SARGABLE query.

If you don't know SARGABLE, your optimization knowledge is incomplete.

---

# Scenario 5 — Pagination Disaster

## Bad

```sql id="9b41tm"
SELECT *
FROM Orders
ORDER BY order_id
LIMIT 20 OFFSET 100000;

Problem¶

DB scans/skips 100k rows first.

Better — Keyset Pagination¶

```sql id="o00sxj" SELECT * FROM Orders WHERE order_id > 100000 ORDER BY order_id LIMIT 20;

This scales far better.

---

# Scenario 6 — JOIN Explosion

## Problem

```sql id="udxuwx"
SELECT *
FROM Customers c
JOIN Orders o
ON c.customer_id = o.customer_id;

Customer has 5000 orders. Result duplicates customer rows 5000 times.

Symptoms¶

huge memory
slow sorting
duplicate amplification
incorrect aggregates

Better¶

Only join what you actually need.

```sql id="of7fgq" SELECT c.customer_id, c.name, COUNT(o.order_id) FROM Customers c LEFT JOIN Orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.name;

---

# Scenario 7 — OR Condition Killing Index

## Bad

```sql id="r19rnp"
SELECT *
FROM Orders
WHERE customer_id = 101
OR status = 'Pending';

Why bad¶

Optimizer may avoid indexes.

Sometimes Better¶

```sql id="9up1r2" SELECT * FROM Orders WHERE customer_id = 101

UNION

SELECT * FROM Orders WHERE status = 'Pending';

Depends on data distribution.

Always validate using execution plan.

---

# Scenario 8 — COUNT(*) on Huge Tables

## Problem

```sql id="u2bj4z"
SELECT COUNT(*)
FROM Orders;

On billion-row tables.

Better Approaches¶

approximate counts
materialized counters
partitioning
cached analytics tables

Production systems rarely run raw COUNT(*) repeatedly at huge scale.

Scenario 9 — NOT IN with NULL Bug¶

Dangerous¶

```sql id="zwjlwm" SELECT * FROM Customers WHERE customer_id NOT IN ( SELECT customer_id FROM Orders );

If subquery contains NULL:

* query may return ZERO rows

This destroys correctness.

---

## Better

```sql id="m9s3yo"
SELECT *
FROM Customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.customer_id = c.customer_id
);

Scenario 10 — Unnecessary DISTINCT¶

Bad¶

```sql id="f5lc1x" SELECT DISTINCT c.name FROM Customers c JOIN Orders o ON c.customer_id = o.customer_id;

DISTINCT often hides bad joins.

---

## Correct Question

Why are duplicates happening?

Fix root cause instead of masking it.

---

# PART 2 — EXISTS vs IN vs JOIN

This is where many developers become cargo-cult programmers.

They memorize:

* EXISTS fast
* IN slow
* JOIN best

That thinking is shallow and often wrong.

You must understand semantics first.

---

# 1. EXISTS

## Purpose

Checks whether matching rows exist.

Returns TRUE/FALSE logically.

---

## Example

```sql id="pnv7xv"
SELECT *
FROM Customers c
WHERE EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.customer_id = c.customer_id
);

Best Use Cases¶

correlated checks
semi-joins
existence testing
large subqueries
NULL-safe logic

Key Characteristic¶

Stops searching after first match.

Very efficient in many cases.

2. IN¶

Purpose¶

Checks membership in a set.

Example¶

```sql id="0h7e95" SELECT * FROM Customers WHERE customer_id IN ( SELECT customer_id FROM Orders );

---

## Best Use Cases

* small subquery result
* static lists
* readable filtering

---

## Risk

`NOT IN` + NULL = dangerous behavior.

---

# 3. JOIN

## Purpose

Combines rows from tables.

---

## Example

```sql id="h2n2fd"
SELECT c.name, o.order_id
FROM Customers c
JOIN Orders o
ON c.customer_id = o.customer_id;

Important¶

JOIN changes row cardinality.

EXISTS does not.

This is a major conceptual difference.

Core Difference¶

Operation	Returns Data?	Changes Row Count?	Best For
EXISTS	No	No	existence check
IN	No	No	membership check
JOIN	Yes	Yes	fetching related data

Example of Wrong JOIN Usage¶

Developer writes:

```sql id="z3g17d" SELECT DISTINCT c.* FROM Customers c JOIN Orders o ON c.customer_id = o.customer_id;

This is actually existence logic.

Better:

```sql id="3ezjvx"
SELECT *
FROM Customers c
WHERE EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.customer_id = c.customer_id
);

Cleaner semantics. Often better execution.

Performance Reality¶

Modern optimizers may internally transform:

IN → EXISTS
EXISTS → SEMI JOIN

So blanket claims are nonsense.

You must evaluate:

data size
indexes
selectivity
execution plan
NULL behavior
cardinality

Rule of Thumb¶

Use EXISTS when:¶

checking existence
correlated logic
avoiding duplicates
large datasets

Use IN when:¶

small lists
readable filters
static membership

Use JOIN when:¶

retrieving columns from both tables
actual relational combination needed

Advanced Insight¶

This query:

```sql id="4f2u73" SELECT * FROM A WHERE EXISTS ( SELECT 1 FROM B WHERE B.id = A.id );

is conceptually a:

* SEMI JOIN

Whereas:

```sql id="5mqqo4"
SELECT *
FROM A
JOIN B
ON A.id = B.id;

is a:

FULL relational join

SQL Practice Set for a 3–5 Year Developer¶

Sample Schema¶

1. Find Top 5 Customers by Total Purchase¶

2. Find Employees Earning More Than Their Manager¶

3. Find Duplicate Customers Based on Name and City¶

4. Get Second Highest Salary¶

5. Find Department-wise Highest Paid Employee¶

6. Find Customers Who Never Ordered¶

7. Monthly Revenue Report¶

8. Find Running Total of Orders¶

9. Find Customers with More Than 3 Orders¶

10. Find Products Never Ordered¶

11. Find Order with Maximum Amount Per Customer¶

12. Find Employees Joined in Last 6 Months¶

13. Count Orders by Status¶

14. Find Average Salary Department-wise¶

15. Find Consecutive Login Days (Advanced Pattern)¶

16. Find Percentage Contribution of Each Product¶

17. Delete Duplicate Rows¶

18. Find Nth Highest Salary¶

19. Pivot Order Status Counts¶

20. Find Latest Order per Customer¶

21. Find Customers with Orders on Consecutive Days¶

22. Calculate Employee Salary Rank Globally¶

23. Find Median Salary¶

24. Detect Missing IDs¶

25. Find Customers Whose Spending Increased Every Month¶

SQL Optimization Scenarios + EXISTS vs IN vs JOIN¶

PART 1 — SQL Optimization Scenarios¶

Scenario 1 — Query Suddenly Becomes Slow¶

Problem¶

2. Check Missing Index¶

Rule¶

Why it’s bad¶

Better¶

Why bad¶

Better¶

Problem¶

Better — Keyset Pagination¶

Symptoms¶

Better¶

Why bad¶

Sometimes Better¶

Better Approaches¶

Scenario 9 — NOT IN with NULL Bug¶

Dangerous¶

Scenario 10 — Unnecessary DISTINCT¶

Bad¶

Best Use Cases¶

Key Characteristic¶

2. IN¶

Purpose¶

Example¶

Important¶

Core Difference¶

Example of Wrong JOIN Usage¶

Performance Reality¶

Rule of Thumb¶

Use EXISTS when:¶

Use IN when:¶

Use JOIN when:¶

Advanced Insight¶