SQL Practice Problems

Practice with these classic interview problems. Each appears frequently at top tech companies. Try solving them before reading the solutions.

Problem 1: Second Highest Salary

Question: Write a query to get the second highest salary from the Employee table. If there is no second highest salary, return NULL.

Employee Table:
| id | salary |
|----|--------|
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |

Solution:

SELECT MAX(salary) as SecondHighestSalary
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);

Alternative using DENSE_RANK:

WITH ranked AS (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rank
    FROM Employee
)
SELECT MAX(salary) as SecondHighestSalary
FROM ranked
WHERE rank = 2;

Problem 2: Consecutive Login Days

Question: Find users who logged in for 3 or more consecutive days.

Logins Table:
| user_id | login_date |
|---------|------------|
| 1       | 2025-01-01 |
| 1       | 2025-01-02 |
| 1       | 2025-01-03 |
| 2       | 2025-01-01 |
| 2       | 2025-01-03 |

Solution:

WITH consecutive_groups AS (
    SELECT
        user_id,
        login_date,
        login_date - INTERVAL '1 day' * ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY login_date
        ) as grp
    FROM (SELECT DISTINCT user_id, login_date FROM Logins) t
)
SELECT DISTINCT user_id
FROM consecutive_groups
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

Key insight: When you subtract a sequential number from consecutive dates, you get the same "group" value. Non-consecutive dates produce different groups.

Problem 3: Top N Per Group

Question: Find the top 2 highest-paid employees in each department.

Employee Table:
| id | name    | salary | department |
|----|---------|--------|------------|
| 1  | Alice   | 90000  | Engineering|
| 2  | Bob     | 85000  | Engineering|
| 3  | Charlie | 80000  | Engineering|
| 4  | Diana   | 70000  | Sales      |

Solution:

WITH ranked AS (
    SELECT
        *,
        DENSE_RANK() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) as rank
    FROM Employee
)
SELECT name, salary, department
FROM ranked
WHERE rank <= 2;

Problem 4: Duplicate Detection

Question: Find all duplicate emails in a Person table.

Person Table:
| id | email            |
|----|------------------|
| 1  | a@example.com    |
| 2  | b@example.com    |
| 3  | a@example.com    |

Solution:

SELECT email
FROM Person
GROUP BY email
HAVING COUNT(*) > 1;

If you need all columns from duplicate rows:

SELECT *
FROM Person
WHERE email IN (
    SELECT email
    FROM Person
    GROUP BY email
    HAVING COUNT(*) > 1
);

Problem 5: Running Totals by Category

Question: Calculate running total of sales by category, ordered by date.

Sales Table:
| date       | category | amount |
|------------|----------|--------|
| 2025-01-01 | A        | 100    |
| 2025-01-02 | A        | 150    |
| 2025-01-01 | B        | 200    |
| 2025-01-02 | B        | 50     |

Solution:

SELECT
    date,
    category,
    amount,
    SUM(amount) OVER (
        PARTITION BY category
        ORDER BY date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as running_total
FROM Sales
ORDER BY category, date;

Practice Strategy

For each problem type, follow this approach:

1. Clarify: Ask about edge cases (NULLs, ties, empty tables)
2. Think aloud: Explain your approach before writing
3. Start simple: Get a working solution first
4. Optimize: Mention if you'd do it differently at scale
5. Verify: Walk through with sample data

Common interview platforms: LeetCode (SQL problems), DataLemur, StrataScratch, HackerRank

Aim to solve 50+ problems before your first interview. The patterns become second nature. :::