SQLProjects and Interview Preparationadvanced30 min read

Solving Common SQL Interview Problems

Solve common SQL interview problems involving Nth-highest salaries, duplicate records, customer retention, active cohorts, ranking, and window functions using PostgreSQL.

On This Page (Table of Contents)

In this lesson, you will learn

✓Find the Nth-highest distinct salary using DENSE_RANK and alternative SQL approaches
✓Understand the differences between ROW_NUMBER, RANK, and DENSE_RANK
✓Identify duplicate records and safely remove duplicates while preserving one canonical row
✓Calculate customer retention and cohort activity using joins, conditional aggregation, and date operations
✓Use window functions to compare current and previous records
✓Explain edge cases involving ties, NULL values, date boundaries, and missing records

Introduction: How to Approach SQL Interview Problems

SQL interview problems often look complicated because they combine several concepts in one question. A reliable approach is to identify the required result first, then select the simplest SQL technique that produces it correctly.

## A four-step approach

1. **Understand the output.** Decide which columns must be returned and whether the result should contain one row, multiple rows, or an aggregate.
2. **Identify the tricky requirement.** Look for words such as "distinct," "Nth highest," "latest," "consecutive," "duplicate," "active," and "retained."
3. **Choose the SQL pattern.** Common tools include GROUP BY, HAVING, joins, subqueries, CTEs, and window functions.
4. **Test edge cases.** Ask what happens when there are ties, NULL values, no matching records, or multiple events on the same date.

This lesson uses PostgreSQL syntax. Some functions and date operations differ between database systems, so adapt the syntax when interviewing for MySQL, SQL Server, or Oracle.

We will solve five major interview patterns:
- Nth-highest distinct salary.
- Detecting and removing duplicates.
- Ranking records and selecting the latest row.
- Customer retention and active cohorts.
- Comparing current and previous activity with window functions.

Problem 1: Find the Nth-Highest Distinct Salary

This is one of the most common SQL interview questions.

**Problem:** Given an employees table, return the third-highest distinct salary.

Sample table:

| employee_id | employee_name | salary |
|---:|---|---:|
| 1 | Asha | 90000 |
| 2 | Ravi | 80000 |
| 3 | Neha | 80000 |
| 4 | Imran | 70000 |
| 5 | Priya | 60000 |

The distinct salary values are:

1. 90000
2. 80000
3. 70000
4. 60000

Therefore, the third-highest distinct salary is 70000.

Notice that Ravi and Neha have the same salary. They occupy the same distinct salary rank. The question asks for the third-highest **distinct salary**, not the third employee after sorting.

## Solution using DENSE_RANK

DENSE_RANK assigns the same rank to equal values and does not leave gaps after ties.

For descending salary:
- 90000 gets rank 1.
- Both 80000 salaries get rank 2.
- 70000 gets rank 3.
- 60000 gets rank 4.

Filter for rank 3 to get the required salary.

Nth-Highest Distinct Salary with DENSE_RANK

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
WITH ranked_salaries AS (
    SELECT
        employee_id,
        employee_name,
        salary,
        DENSE_RANK() OVER (
            ORDER BY salary DESC
        ) AS salary_rank
    FROM employees
    WHERE salary IS NOT NULL
)
SELECT
    employee_id,
    employee_name,
    salary
FROM ranked_salaries
WHERE salary_rank = 3
ORDER BY employee_id;

Output

employee_id | employee_name | salary ------------+---------------+------- 4 | Imran | 70000

The query assumes the sample employees table has been created and populated. DENSE_RANK gives equal salaries the same rank, so 70000 is rank 3. Filtering NULL salaries avoids treating missing salary values as a ranked salary.

Understanding ROW_NUMBER, RANK, and DENSE_RANK

These three window functions are similar, but their treatment of ties differs.

Consider salaries sorted from highest to lowest: 90000, 80000, 80000, 70000.

| Salary | ROW_NUMBER | RANK | DENSE_RANK |
|---:|---:|---:|---:|
| 90000 | 1 | 1 | 1 |
| 80000 | 2 | 2 | 2 |
| 80000 | 3 | 2 | 2 |
| 70000 | 4 | 4 | 3 |

### ROW_NUMBER

Assigns a different sequence number to every row. If rows tie on the ordering column, the order between them is unspecified unless you add a tie-breaker.

Use it when you need exactly one row number per record, such as selecting the latest event for each customer.

### RANK

Assigns the same rank to ties, then leaves gaps. In the example, 70000 has rank 4 because two employees share rank 2.

Use it when competition-style ranking is required.

### DENSE_RANK

Assigns the same rank to ties without leaving gaps. In the example, 70000 has rank 3.

Use it for the Nth-highest **distinct** value.

## Interview rule

If the interviewer says "Nth-highest distinct salary," DENSE_RANK is usually the clearest choice.

If the interviewer says "Nth row after sorting," ROW_NUMBER may be appropriate.

If the interviewer says "rank employees and preserve competition-style positions," RANK may be appropriate.

Compare the Three Ranking Functions

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT
    employee_name,
    salary,
    ROW_NUMBER() OVER (
        ORDER BY salary DESC, employee_id
    ) AS row_num,
    RANK() OVER (
        ORDER BY salary DESC
    ) AS salary_rank,
    DENSE_RANK() OVER (
        ORDER BY salary DESC
    ) AS dense_salary_rank
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC, employee_id;

Output

employee_name | salary | row_num | salary_rank | dense_salary_rank --------------+--------+---------+-------------+------------------ Asha | 90000 | 1 | 1 | 1 Ravi | 80000 | 2 | 2 | 2 Neha | 80000 | 3 | 2 | 2 Imran | 70000 | 4 | 4 | 3 Priya | 60000 | 5 | 5 | 4

ROW_NUMBER uses employee_id as a tie-breaker, so the row numbers are deterministic for this sample. RANK and DENSE_RANK order only by salary, so equal salaries share a rank.

Alternative: Nth-Highest Salary with DISTINCT and LIMIT

For PostgreSQL, you can also select the Nth-highest distinct salary by sorting the distinct values and using OFFSET and LIMIT.

The OFFSET value is N - 1 because SQL offsets start at zero:
- First-highest: OFFSET 0.
- Second-highest: OFFSET 1.
- Third-highest: OFFSET 2.

This is concise when the required rank is fixed. DENSE_RANK is often easier to extend when the question also asks for employee details or all employees sharing the Nth-highest salary.

If there are fewer than N distinct non-NULL salaries, the query returns no row.

Third-Highest Distinct Salary with LIMIT

sql

1
2
3
4
5
6
SELECT DISTINCT salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
OFFSET 2
LIMIT 1;

Output

salary ------ 70000

DISTINCT removes repeated salary values before sorting. OFFSET 2 skips the first two distinct salaries, and LIMIT 1 returns the next value. The syntax is supported by PostgreSQL and several other databases, though pagination syntax varies by system.

Returning Only the Nth-Highest Salary Value

Some coding platforms expect a single output column rather than employee details.

You can use a scalar subquery to return the third-highest distinct salary. If there are fewer than three distinct non-NULL salaries, the scalar subquery evaluates to NULL.

This is useful when the problem statement requires a single value and specifies what to return when the Nth value does not exist.

Scalar Result for the Third-Highest Salary

sql

1
2
3
4
5
6
7
8
SELECT (
    SELECT DISTINCT salary
    FROM employees
    WHERE salary IS NOT NULL
    ORDER BY salary DESC
    OFFSET 2
    LIMIT 1
) AS third_highest_salary;

Output

third_highest_salary -------------------- 70000

The outer SELECT returns one row with one column. If the inner query returns no rows, the scalar subquery returns NULL. Check the interview problem's required behavior because some platforms expect no row or a specific sentinel instead.

Problem 2: Identify Duplicate Records

A duplicate is defined by the business rule, not necessarily by every column being identical.

For example, if email must be unique, two customers with the same email are duplicates for that rule, even if their names or creation timestamps differ.

Sample customers:

| customer_id | full_name | email |
|---:|---|---|
| 1 | Asha | asha@example.com |
| 2 | Ravi | ravi@example.com |
| 3 | Asha Sharma | asha@example.com |
| 4 | Neha | neha@example.com |

Email asha@example.com appears twice.

GROUP BY and HAVING are the standard way to identify duplicated values. HAVING filters groups after aggregation, unlike WHERE, which filters individual rows before grouping.

Find Duplicate Emails

sql

1
2
3
4
5
6
7
8
SELECT
    email,
    COUNT(*) AS occurrence_count
FROM customers
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY email;

Output

email | occurrence_count ------------------+----------------- asha@example.com | 2

The query groups customers by email and keeps only groups containing more than one row. It ignores NULL emails here because this example is specifically checking repeated, non-NULL email values.

Problem 3: Find and Remove Duplicate Rows Safely

Deleting duplicates is more dangerous than finding them. First decide which record should survive.

For this example, the rule is:
- Records with the same email are considered duplicates.
- Keep the row with the smallest customer_id.
- Delete the remaining rows.

Before deleting anything:
1. Run a SELECT that shows the rows to be deleted.
2. Confirm the survivor rule with the business owner.
3. Back up the data or use an appropriate recovery plan.
4. Perform the operation in a transaction where practical.
5. Check the affected row count before committing.

PostgreSQL provides the system column ctid, which identifies a physical row version within a table. It can be useful for a controlled cleanup when the table has no suitable unique key, but it is not a permanent identifier and should not be stored as one.

Here, customer_id is a stable primary key, so we can safely identify duplicate rows by that key.

Preview Duplicate Rows Before Deleting

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
WITH ranked_customers AS (
    SELECT
        customer_id,
        full_name,
        email,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY customer_id
        ) AS duplicate_number
    FROM customers
    WHERE email IS NOT NULL
)
SELECT
    customer_id,
    full_name,
    email,
    duplicate_number
FROM ranked_customers
WHERE duplicate_number > 1
ORDER BY email, customer_id;

Output

ROW_NUMBER assigns a sequence within each email group. The smallest customer_id receives number 1 and is kept. Rows with duplicate_number greater than 1 are candidates for deletion under this particular survivor rule.

Deleting Duplicates in a Transaction

Once the preview is verified, PostgreSQL can delete rows identified by a CTE.

The following example is designed for the sample customer data. It keeps the smallest customer_id for each non-NULL email and deletes additional rows.

Do not run this blindly against production data. Before deletion, check whether other tables reference the duplicate customer IDs. Those foreign-key relationships may need to be migrated to the surviving customer before the duplicates can be removed.

Delete Extra Rows While Keeping One Record

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
BEGIN;
 
WITH ranked_customers AS (
    SELECT
        customer_id,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY customer_id
        ) AS duplicate_number
    FROM customers
    WHERE email IS NOT NULL
)
DELETE FROM customers AS c
USING ranked_customers AS r
WHERE c.customer_id = r.customer_id
  AND r.duplicate_number > 1
RETURNING c.customer_id, c.full_name, c.email;
 
-- Inspect the returned rows and affected row count.
-- COMMIT only after verifying the deletion.
-- Otherwise run ROLLBACK.
 
ROLLBACK;

Output

customer_id | full_name | email ------------+-------------+------------------ 3 | Asha Sharma | asha@example.com ROLLBACK

The example deliberately ends with ROLLBACK so the demonstration does not permanently delete the sample duplicate. In a real cleanup, review the returned rows, reconcile dependent foreign keys, and commit only after verification. The survivor rule is based on customer_id, not which name looks more complete.

Preventing Duplicates with Constraints

A cleanup query fixes existing duplicate data, but a constraint prevents future violations of the same rule.

If email must be unique, a UNIQUE constraint is usually the correct long-term solution. Before adding it, clean up existing duplicates and define how NULL and case differences should be handled.

In PostgreSQL, a normal unique constraint permits multiple NULL values because NULL values are not treated as equal for ordinary uniqueness checks. If every customer must have an email, combine NOT NULL with UNIQUE.

Email addresses can also differ only by case or surrounding whitespace. Whether those should be considered duplicates is a business decision. Normalize input in the application or use an appropriate PostgreSQL design, such as a unique index on lower(email), when case-insensitive uniqueness is required.

Enforce Unique Email Values

sql

1
2
3
4
5
6
7
8
9
10
-- Run only after existing duplicates have been resolved.
ALTER TABLE customers
ALTER COLUMN email SET NOT NULL;
 
ALTER TABLE customers
ADD CONSTRAINT customers_email_unique UNIQUE (email);
 
-- Optional PostgreSQL approach for case-insensitive uniqueness:
CREATE UNIQUE INDEX customers_email_lower_unique
ON customers (LOWER(email));

Output

ALTER TABLE ALTER TABLE CREATE INDEX

Do not add both uniqueness mechanisms without considering your intended rule. A normal UNIQUE constraint and a unique index on LOWER(email) enforce different rules: the latter prevents values that differ only by case. The case-insensitive index will fail to build if existing emails collide after lowercasing.

Problem 4: Find the Latest Record for Each Customer

A common interview task is to find each customer's latest order.

MAX(order_date) returns the latest timestamp, but if you need other columns from the same order, you must identify the entire row. A window function is a convenient solution.

Use ROW_NUMBER partitioned by customer and ordered by order date descending. Add order_id as a tie-breaker so the result is deterministic when two orders share a timestamp.

The result contains at most one order per customer. Customers with no orders do not appear in this query because the ranking starts from the orders table.

Latest Order for Each Customer

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
WITH ranked_orders AS (
    SELECT
        order_id,
        customer_id,
        order_date,
        status,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY order_date DESC, order_id DESC
        ) AS row_num
    FROM orders
)
SELECT
    order_id,
    customer_id,
    order_date,
    status
FROM ranked_orders
WHERE row_num = 1
ORDER BY customer_id;

Output

order_id | customer_id | order_date | status ---------+-------------+---------------------+----------- 3 | 1 | 2026-10-02 09:15:00 | confirmed 5 | 2 | 2026-10-15 14:00:00 | cancelled 4 | 3 | 2026-10-10 16:45:00 | pending

ROW_NUMBER resets for each customer. The newest order gets row_num = 1. This result includes cancelled orders because it asks for the latest order of any status. Filter status before ranking only if the requirement is specifically the latest qualifying order.

Problem 5: Customer Cohorts and Retention

A cohort is a group of users who share a starting characteristic, often the month of their first purchase or registration.

Retention measures how many members of that group return or remain active in a later period.

A useful definition must specify:
- Cohort assignment: for example, the month of the customer's first order.
- Activity: for example, placing any order.
- Retention period: for example, the month after the cohort month.
- Counting rule: distinct customers, not the number of orders.

For this lesson, we define:
- Cohort month = month of a customer's first order.
- Activity month = month in which the customer placed an order.
- Month 0 = the cohort month.
- Month 1 = the next calendar month.

A customer counts at most once per cohort/activity month, even if they place several orders during that month.

This is a simplified order-activity metric. A paid-purchase retention report would need to filter orders using an explicit successful-payment definition.

Build a Monthly Customer Cohort Table

First, calculate each customer's first order timestamp. Then assign the customer to a cohort month.

Next, collect distinct customer activity by calendar month and calculate the number of months between cohort month and activity month.

PostgreSQL date operations can calculate the month offset using year and month components. The expression below works for month-level cohort analysis and does not depend on the number of days between two dates.

The query returns one row per cohort month and activity-month offset, with both the number of active customers and the original cohort size.

Monthly Cohort Retention

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
WITH first_orders AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', MIN(order_date))::date AS cohort_month
    FROM orders
    GROUP BY customer_id
),
customer_activity AS (
    SELECT DISTINCT
        customer_id,
        DATE_TRUNC('month', order_date)::date AS activity_month
    FROM orders
),
cohort_activity AS (
    SELECT
        f.cohort_month,
        a.activity_month,
        (
            EXTRACT(YEAR FROM a.activity_month)
            - EXTRACT(YEAR FROM f.cohort_month)
        ) * 12
        + (
            EXTRACT(MONTH FROM a.activity_month)
            - EXTRACT(MONTH FROM f.cohort_month)
        ) AS month_number,
        a.customer_id
    FROM first_orders AS f
    JOIN customer_activity AS a
        ON a.customer_id = f.customer_id
    WHERE a.activity_month >= f.cohort_month
),
cohort_sizes AS (
    SELECT
        cohort_month,
        COUNT(*) AS cohort_size
    FROM first_orders
    GROUP BY cohort_month
)
SELECT
    ca.cohort_month,
    ca.month_number,
    cs.cohort_size,
    COUNT(DISTINCT ca.customer_id) AS active_customers,
    ROUND(
        100.0 * COUNT(DISTINCT ca.customer_id)
        / NULLIF(cs.cohort_size, 0),
        2
    ) AS retention_percent
FROM cohort_activity AS ca
JOIN cohort_sizes AS cs
    ON cs.cohort_month = ca.cohort_month
GROUP BY
    ca.cohort_month,
    ca.month_number,
    cs.cohort_size
ORDER BY ca.cohort_month, ca.month_number;

Output

cohort_month | month_number | cohort_size | active_customers | retention_percent -------------+--------------+-------------+------------------+------------------ 2026-09-01 | 0 | 2 | 2 | 100.00 2026-10-01 | 0 | 2 | 2 | 100.00

In the sample data, customers 1 and 2 first ordered in September, while customer 3 first ordered in October. Customer 4 has no order and therefore has no purchase cohort. The sample has no later-month activity for the September cohort, so no September month-1 row is produced. A full retention matrix often generates missing month offsets explicitly so those periods can be displayed as zero or not yet observable.

Understanding Cohort Maturity and Missing Periods

A cohort report should not automatically treat every missing future month as zero retention.

For example, if a cohort began in October and the report runs in October, November retention has not yet been observed. It is not correct to interpret this as zero retention.

A mature cohort has had enough time to reach the retention period being measured. Analysts often:
- Show only periods that have elapsed.
- Mark future periods as NULL or not yet observable.
- Use a generated calendar of month offsets to fill missing periods for mature cohorts.

Also note that cohort size can differ depending on whether the cohort is defined by registration, first order, or first successful purchase. Always document the definition alongside the report.

Problem 6: Compare Current and Previous Orders

LAG returns a value from a previous row in the ordered partition. It is useful when an interview asks you to compare a record with the previous event.

Here, we compare each customer's order timestamp with their previous order timestamp.

The first order for each customer has no previous order, so previous_order_date is NULL. The number of days between timestamps can be calculated by subtracting the previous timestamp from the current timestamp.

Compare Each Order with the Previous Order

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
WITH order_history AS (
    SELECT
        order_id,
        customer_id,
        order_date,
        LAG(order_date) OVER (
            PARTITION BY customer_id
            ORDER BY order_date, order_id
        ) AS previous_order_date
    FROM orders
)
SELECT
    order_id,
    customer_id,
    order_date,
    previous_order_date,
    order_date - previous_order_date AS time_since_previous_order
FROM order_history
ORDER BY customer_id, order_date, order_id;

Output

LAG is calculated independently for each customer. The first order in each partition has no previous row. PostgreSQL timestamp subtraction returns an interval, not an integer count of days.

Problem 7: Find Customers with Consecutive Active Months

Another common interview question asks which customers were active in consecutive months.

First, reduce the data to one row per customer per month. This prevents multiple orders in one month from being counted as separate active months.

Then use LAG to compare each active month with the customer's previous active month. A difference of one month indicates consecutive calendar months.

This query finds customers who were active in two consecutive months. To find three or more consecutive months, extend the logic to count runs or use a gaps-and-islands pattern.

Find Consecutive Active Months

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
WITH monthly_activity AS (
    SELECT DISTINCT
        customer_id,
        DATE_TRUNC('month', order_date)::date AS activity_month
    FROM orders
),
activity_with_previous AS (
    SELECT
        customer_id,
        activity_month,
        LAG(activity_month) OVER (
            PARTITION BY customer_id
            ORDER BY activity_month
        ) AS previous_active_month
    FROM monthly_activity
)
SELECT
    customer_id,
    previous_active_month,
    activity_month
FROM activity_with_previous
WHERE activity_month =
      (previous_active_month + INTERVAL '1 month')::date
ORDER BY customer_id, activity_month;

Output

No rows returned for the sample data.

The sample customers are active in September or October, but none has orders in both months. DISTINCT ensures that multiple orders during one month do not create false consecutive-month activity.

Practice Problems

Try these interview-style problems before reading the solutions.

## Exercise 1: Second-highest salary
Return the second-highest distinct non-NULL salary. If no such salary exists, return NULL.

## Exercise 2: All employees sharing the highest salary
Return every employee whose salary equals the maximum non-NULL salary.

## Exercise 3: Duplicate phone numbers
Find phone numbers that appear more than once, ignoring NULL phone numbers.

## Exercise 4: Keep the newest customer record
A staging table contains repeated external_customer_id values. Select the newest record for each external customer, breaking timestamp ties with the largest staging_id.

## Exercise 5: Customers with no orders
Return all customers who have never placed an order.

## Exercise 6: Customer order count
Return every customer and their order count, including customers with zero orders.

## Exercise 7: Month-over-month order activity
Count distinct active customers per month and show the difference from the previous month.

## Exercise 8: Cohort retention
Explain why a month-1 retention rate cannot be considered zero when the cohort's first month is the latest month in the report.

## Exercise 9: Safe duplicate deletion
Before deleting duplicates, identify the survivor rule and determine whether any foreign keys reference the rows that would be removed.

## Exercise 10: Explain the ranking functions
Using a dataset with tied salaries, show how ROW_NUMBER, RANK, and DENSE_RANK produce different results.

Practice Problem Solutions

These are reference solutions. The sample employee, customer, and staging tables are illustrative; adapt column names to your actual schema.

Important details:
- Exercise 1 returns a single row with NULL if there is no second distinct salary.
- Exercise 2 returns every employee tied at the maximum salary.
- Exercise 4 uses ROW_NUMBER with a deterministic tie-breaker.
- Exercise 6 uses LEFT JOIN so customers with no orders remain in the output.
- Exercise 7 uses a monthly aggregate before applying LAG.

Reference SQL Solutions

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
-- Exercise 1: Second-highest distinct salary
SELECT (
    SELECT DISTINCT salary
    FROM employees
    WHERE salary IS NOT NULL
    ORDER BY salary DESC
    OFFSET 1
    LIMIT 1
) AS second_highest_salary;
 
-- Exercise 2: Every employee with the highest salary
SELECT employee_id, employee_name, salary
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
);
 
-- Exercise 3: Duplicate non-NULL phone numbers
SELECT phone, COUNT(*) AS occurrence_count
FROM customers
WHERE phone IS NOT NULL
GROUP BY phone
HAVING COUNT(*) > 1;
 
-- Exercise 4: Newest staging row per external customer
WITH ranked_staging AS (
    SELECT
        staging_id,
        external_customer_id,
        updated_at,
        ROW_NUMBER() OVER (
            PARTITION BY external_customer_id
            ORDER BY updated_at DESC NULLS LAST, staging_id DESC
        ) AS row_num
    FROM customer_staging
)
SELECT *
FROM ranked_staging
WHERE row_num = 1;
 
-- Exercise 5: Customers with no orders
SELECT c.customer_id, c.full_name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
);
 
-- Exercise 6: Every customer and order count
SELECT
    c.customer_id,
    c.full_name,
    COUNT(o.order_id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.full_name
ORDER BY c.customer_id;
 
-- Exercise 7: Monthly active customers and change
WITH monthly_counts AS (
    SELECT
        DATE_TRUNC('month', order_date)::date AS activity_month,
        COUNT(DISTINCT customer_id) AS active_customers
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)::date
)
SELECT
    activity_month,
    active_customers,
    active_customers - LAG(active_customers) OVER (
        ORDER BY activity_month
    ) AS change_from_previous_month
FROM monthly_counts
ORDER BY activity_month;

Output

Exercise 1: second_highest_salary --------------------- 80000 Exercise 2: Asha | 90000 Exercise 3: No duplicate phone numbers are shown in the sample data. Exercise 5: No rows returned for the sample data, because every sample customer except Meera? Correction: Meera has never ordered, so Meera is returned. Exercise 6: Each customer appears once, including Meera with an order_count of 0. Exercise 7: 2026-09-01 | 2 | NULL 2026-10-01 | 3 | 1

The exact outputs depend on the tables and sample rows used. Exercise 5 returns Meera Gupta because she has no orders in the sample dataset. Exercise 7 counts distinct customers with orders in each month: two in September and three in October. LAG returns NULL for the first month because there is no preceding month in the query result.

SQL Interview Questions and Answers

## 1. What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping. HAVING filters groups after GROUP BY has calculated aggregates.

## 2. When should you use DENSE_RANK instead of ROW_NUMBER?

Use DENSE_RANK when tied values should share a rank and distinct values should have consecutive ranks. Use ROW_NUMBER when every row needs a unique sequence number.

## 3. What is the difference between RANK and DENSE_RANK?

RANK leaves gaps after ties. DENSE_RANK does not. For values 100, 90, 90, 80, their ranks are 1, 2, 2, 4 with RANK and 1, 2, 2, 3 with DENSE_RANK.

## 4. How do you find duplicate records?

Group by the columns that define a duplicate and use HAVING COUNT(*) > 1. The definition of duplicate must come from the business rule.

## 5. How do you remove duplicates safely?

Rank rows inside each duplicate group using ROW_NUMBER and a deterministic ordering. Preview the rows to delete, check dependencies, and use a transaction or recovery plan where appropriate.

## 6. Why is a unique constraint better than repeated cleanup?

A unique constraint prevents future duplicates that violate the chosen uniqueness rule. Cleanup alone only fixes the data that already exists.

## 7. How do you find the latest row per customer?

Use ROW_NUMBER partitioned by customer_id and ordered by the timestamp descending, with a deterministic tie-breaker. Keep row number 1.

## 8. What is a cohort?

A cohort is a group sharing a defined starting characteristic, such as the month of their first purchase.

## 9. What does customer retention measure?

It measures how many members of a cohort return or remain active in a later period, according to a defined activity rule.

## 10. Why use COUNT(DISTINCT customer_id) in a retention query?

A customer may perform several actions during one period. Counting distinct customer IDs prevents that customer from being counted more than once in the same cohort-period cell.

## 11. What does LAG do?

LAG retrieves a value from a previous row in the same window partition according to the specified ordering.

## 12. What is a gaps-and-islands problem?

It is a class of problems involving consecutive or continuous groups of values, such as consecutive dates or months. Window functions and date arithmetic are common ways to solve them.

## 13. Why must window ordering be deterministic for ROW_NUMBER?

If multiple rows tie on all ordering columns, their relative order is unspecified. Add a unique tie-breaker when selecting a specific row.

## 14. What happens if the Nth-highest salary does not exist?

The behavior depends on the query. A scalar subquery using OFFSET and LIMIT returns NULL when no value exists; a ranked-row query returns no row.

## 15. Why should NULL salaries often be excluded from ranking?

A missing salary is not a meaningful numeric salary. Excluding NULL makes the ranking rule explicit and avoids engine-specific surprises in NULL sort order.

## 16. Why can date-based retention queries be misleading?

Retention depends on the cohort definition, activity event, time zone, period boundaries, and whether each cohort has had enough time to mature.

## 17. What is the difference between NOT EXISTS and NOT IN?

NOT EXISTS tests whether a matching row exists. NOT IN can produce unexpected results if its subquery contains NULL, so NOT EXISTS is often easier to reason about for anti-join problems.

## 18. How would you make duplicate cleanup safe in a large production table?

Define the survivor rule, identify dependencies, back up or stage affected data, test the query, consider batching to reduce lock duration, enforce the final uniqueness rule, and monitor the operation.

Common Mistakes and Best Practices

## Mistake 1: Using ROW_NUMBER for the Nth-highest distinct salary

ROW_NUMBER counts rows, not distinct salary values. Tied salaries receive separate row numbers.

**Better:** use DENSE_RANK for the Nth-highest distinct value.

## Mistake 2: Forgetting NULL salaries

A missing salary should not normally count as a salary rank.

**Better:** add WHERE salary IS NOT NULL when that matches the problem definition.

## Mistake 3: Deleting duplicates without previewing them

The wrong survivor rule can remove a more complete or more recent record.

**Better:** preview the rows, choose a deterministic survivor, inspect foreign-key dependencies, and verify the affected row count.

## Mistake 4: Assuming all identical-looking rows are duplicates

Two transactions may have the same amount and timestamp but still represent distinct valid events.

**Better:** define duplicates using business keys or source event identifiers.

## Mistake 5: Using MAX(timestamp) when other columns from that row are required

MAX returns a value, not the complete row associated with that value.

**Better:** use ROW_NUMBER or a carefully designed join to identify the row.

## Mistake 6: Counting orders instead of customers in retention

Several orders from one customer can inflate the activity count.

**Better:** use COUNT(DISTINCT customer_id) after defining the cohort-period activity.

## Mistake 7: Ignoring ties in window ordering

If two events have the same timestamp, selecting a row with ROW_NUMBER can be nondeterministic unless the ordering includes a tie-breaker.

**Better:** order by timestamp and a unique ID.

## Mistake 8: Treating unobserved future periods as zero retention

A recent cohort may not have reached the next period.

**Better:** distinguish zero observed activity from a period that has not yet elapsed.

## Mistake 9: Assuming a query works identically in every SQL database

Window functions are widely supported, but pagination syntax, date functions, deletion syntax, and NULL ordering vary.

**Better:** confirm the target database and state the dialect during an interview.

A Reliable Pattern for Selecting the Latest Row

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
WITH ranked_events AS (
    SELECT
        event_id,
        customer_id,
        event_timestamp,
        event_type,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY event_timestamp DESC NULLS LAST, event_id DESC
        ) AS row_num
    FROM customer_events
)
SELECT
    event_id,
    customer_id,
    event_timestamp,
    event_type
FROM ranked_events
WHERE row_num = 1;

Output

Returns at most one row per customer that has at least one event.

The event timestamp is sorted newest first, NULL timestamps are placed last, and event_id provides a tie-breaker. If the table contains a customer with only NULL timestamps, that customer's row with the greatest event_id will still be selected. Change the filter if rows with missing timestamps should be excluded entirely.

Lesson Summary

You should now be able to recognize and solve several common SQL interview patterns.

Remember these mappings:
- Nth-highest distinct value → DENSE_RANK or DISTINCT with ORDER BY, OFFSET, and LIMIT.
- Unique row number per partition → ROW_NUMBER.
- Competition ranking with gaps → RANK.
- Find duplicate values → GROUP BY and HAVING COUNT(*) > 1.
- Keep one row per duplicate group → ROW_NUMBER with a deterministic survivor rule.
- Latest row per group → ROW_NUMBER partitioned by the group key and ordered by timestamp.
- Customer cohort retention → first-activity cohort, distinct period activity, and cohort-size denominator.
- Compare with the previous row → LAG.
- Customers with no related records → NOT EXISTS or a correctly written anti-join.

The strongest interview answers explain not only how the query works, but also why it handles ties, NULL values, missing records, and duplicate events correctly.

Common pitfalls to avoid

Real debugging lessons and exact syntax contrasts to avoid runtime bugs

#1 Using ROW_NUMBER to find the Nth-highest distinct salary.

Why it happens: ROW_NUMBER assigns a separate number to each row, so employees sharing a salary occupy different positions.

Incorrect Approach

sql

SELECT salary
FROM (
    SELECT
        salary,
        ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
    FROM employees
    WHERE salary IS NOT NULL
) AS ranked
WHERE rn = 3;

Correct Solution

sql

SELECT salary
FROM (
    SELECT
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
    FROM employees
    WHERE salary IS NOT NULL
) AS ranked
WHERE salary_rank = 3;

Key takeaway: DENSE_RANK assigns the same rank to tied salaries and gives the next distinct salary the next consecutive rank.

#2 Deleting duplicate records without a deterministic survivor rule.

Why it happens: A cleanup query may delete records arbitrarily or preserve a less useful record when several rows share the same business key.

Incorrect Approach

sql

-- Do not delete rows based only on a duplicate-looking value
-- without defining which row should survive.
DELETE FROM customers
WHERE email IN (
    SELECT email
    FROM customers
    GROUP BY email
    HAVING COUNT(*) > 1
);

Correct Solution

sql

WITH ranked_customers AS (
    SELECT
        customer_id,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY customer_id
        ) AS row_num
    FROM customers
    WHERE email IS NOT NULL
)
SELECT customer_id
FROM ranked_customers
WHERE row_num > 1;

Key takeaway: First preview duplicate candidates and define which row to keep. The corrected example selects candidates only; deletion should follow after dependency checks and verification.

#3 Counting orders instead of distinct customers for retention.

Why it happens: One customer may place multiple orders during the same period, causing order counts to overstate the number of retained customers.

Incorrect Approach

sql

SELECT
    DATE_TRUNC('month', order_date)::date AS activity_month,
    COUNT(*) AS active_customers
FROM orders
GROUP BY DATE_TRUNC('month', order_date)::date;

Correct Solution

sql

SELECT
    DATE_TRUNC('month', order_date)::date AS activity_month,
    COUNT(DISTINCT customer_id) AS active_customers
FROM orders
GROUP BY DATE_TRUNC('month', order_date)::date
ORDER BY activity_month;

Key takeaway: COUNT(DISTINCT customer_id) counts each customer once per month. A complete cohort retention report must also group activity by cohort and calculate the appropriate denominator.

#4 Selecting a latest row without resolving timestamp ties.

Why it happens: Several rows can have the same timestamp, so ordering only by timestamp does not fully determine which row receives row number 1.

Incorrect Approach

sql

ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY event_timestamp DESC
)

Correct Solution

sql

ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY event_timestamp DESC NULLS LAST, event_id DESC
)

Key takeaway: Add a stable tie-breaker such as a unique event ID. This makes the row-selection rule deterministic for equal timestamps.

#5 Treating a cohort's unobserved future month as zero retention.

Why it happens: Analysts may create a retention table without distinguishing periods that have elapsed from periods that have not yet occurred.

Incorrect Approach

sql

-- A missing month is not automatically evidence
-- that a cohort had zero retention.

Correct Solution

sql

-- Define the report's observation cutoff.
-- Report only elapsed periods, or mark future periods
-- as NULL / not yet observable.

Key takeaway: Retention is meaningful only when the cohort has had an opportunity to reach the period being measured. Missing activity and unavailable future data are different states.

Ready for hands-on challenges?

Test your skills with shuffled coding challenges in the Practice Arena.

Practice Arena →

Key takeaways

DENSE_RANK assigns consecutive ranks to distinct values, making it useful for Nth-highest distinct salary problems.
ROW_NUMBER assigns a unique sequence to rows, while RANK and DENSE_RANK handle ties differently.
Identify duplicates with GROUP BY and HAVING before deciding which records should be deleted.
Use ROW_NUMBER with a deterministic ordering to select one record from each duplicate group.
Retention queries require a clear definition of a cohort, an activity event, and the retention period.
Date boundaries, NULL values, ties, and duplicate events can change query results and must be handled deliberately.

Was this lesson helpful?

Found an error? Report an issue