10 Essential MySQL Interview Queries (Part 3) – Solutions and Explanations

1. Find users with three or more consecutive login days

Table: user_login_log(user_id, login_date) (unique dates per user).

WITH ranked AS (
    SELECT
        user_id,
        login_date,
        DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS grp
    FROM user_login_log
)
SELECT DISTINCT user_id
FROM ranked
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

Use ROW_NUMBER() to assign a sequential index to each login date per user.

Subtract the index from the date; consecutive dates produce the same grp value.

Group by user_id and grp; keep groups where the count is at least three.

✅ Assessment point: window functions, date arithmetic, modeling continuity.

2. Retrieve the last page of a paginated query and why OFFSET is slow on large tables

Goal: fetch the final page of 10 rows when the total row count is unknown.

Unrecommended approach (high cost on millions of rows):

SELECT * FROM orders ORDER BY id DESC LIMIT 10 OFFSET 999990;

Optimized approach (determine the start ID of the last page first):

SELECT * FROM orders
WHERE id <= (
    SELECT id FROM orders ORDER BY id DESC LIMIT 1 OFFSET 9
)
ORDER BY id DESC
LIMIT 10;

Cursor pagination (when the previous page's minimum id is known as last_id ):

SELECT * FROM orders
WHERE id < last_id
ORDER BY id DESC
LIMIT 10;

OFFSET N

forces the engine to scan the first N rows; it cannot skip them.

Cursor pagination leverages the index order, achieving O(logN + K) complexity.

✅ Assessment point: pagination performance, index utilization, large‑data query optimization.

3. List the highest‑paid employee(s) per department, including ties

Table: employees(id, name, dept_id, salary) Window‑function solution (preferred):

SELECT id, name, dept_id, salary
FROM (
    SELECT *, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk
    FROM employees
) t
WHERE rk = 1;

Correlated subquery alternative (less efficient):

SELECT e1.*
FROM employees e1
WHERE e1.salary = (
    SELECT MAX(e2.salary)
    FROM employees e2
    WHERE e2.dept_id = e1.dept_id
);

✅ Assessment point: window functions vs. correlated subqueries, handling ties.

4. Find customers who have never placed an order (two approaches and performance comparison)

Tables: customers(id, name), orders(customer_id, ...) Approach 1 – LEFT JOIN + IS NULL:

SELECT c.*
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.customer_id IS NULL;

Approach 2 – NOT EXISTS:

SELECT *
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

If orders.customer_id is indexed, both queries have similar performance. NOT EXISTS is semantically clearer and avoids duplicate rows that a join could produce. NOT IN is discouraged because NULL values can cause the result set to be empty.

✅ Assessment point: anti‑join techniques, NULL safety, execution‑plan understanding.

5. Compute a running total (cumulative sales by date)

Table: sales(sale_date, amount) MySQL 8+ using window functions:

SELECT
    sale_date,
    amount,
    SUM(amount) OVER (ORDER BY sale_date ROWS UNBOUNDED PRECEDING) AS running_total
FROM sales
ORDER BY sale_date;

MySQL 5.7 compatible version using user‑defined variables (order‑dependent, not stable):

SET @running_total := 0;
SELECT
    sale_date,
    amount,
    (@running_total := @running_total + amount) AS running_total
FROM sales
ORDER BY sale_date;

✅ Assessment point: window functions, cumulative calculations.

6. Retrieve each user's most recent order efficiently (avoid subquery explosion)

Table: orders(user_id, order_id, create_time, ...) Efficient window‑function solution:

SELECT user_id, order_id, create_time, ...
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time DESC) AS rn
    FROM orders
) t
WHERE rn = 1;

Inefficient approach (runs a subquery per user, O(N²)):

SELECT o1.*
FROM orders o1
WHERE o1.create_time = (
    SELECT MAX(o2.create_time)
    FROM orders o2
    WHERE o2.user_id = o1.user_id
);

✅ Assessment point: Top‑N per group, performance advantage of window functions.

7. Detect circular references in a hierarchy (e.g., A→B→C→A)

Table: org(id, parent_id) representing parent‑child links.

MySQL 8.0 recursive CTE solution:

WITH RECURSIVE org_path AS (
    -- Anchor: start from every node
    SELECT id, parent_id,
           CAST(id AS CHAR(1000)) AS path,
           0 AS depth
    FROM org
    UNION ALL
    -- Recursive step: walk up to parent
    SELECT o.id, o.parent_id,
           CONCAT(p.path, ',', o.parent_id), p.depth + 1
    FROM org o
    INNER JOIN org_path p ON o.id = p.parent_id
    WHERE p.depth < 10               -- prevent infinite recursion
      AND FIND_IN_SET(o.parent_id, p.path) = 0  -- avoid loops
)
SELECT DISTINCT id
FROM org_path
WHERE FIND_IN_SET(id, path) > 1;

✅ Assessment point: recursive queries, graph traversal, loop‑prevention design.

8. Pivot rows to columns (e.g., expand student scores by subject)

Source data example:

student | subject | score
--------+---------+-------
Alice   | Math    | 90
Alice   | English | 85

Target layout: one row per student with separate columns for each subject.

Conditional aggregation solution:

SELECT
    student,
    MAX(CASE WHEN subject = 'Math'    THEN score END)    AS Math,
    MAX(CASE WHEN subject = 'English' THEN score END) AS English
FROM scores
GROUP BY student;

✅ Assessment point: dynamic column generation, CASE WHEN usage, aggregation tricks.

9. Identify duplicate records and keep only the latest entry

Table: logs(id, content, create_time); rows with identical content are duplicates.

Deletion using window functions:

DELETE l1
FROM logs l1
INNER JOIN (
    SELECT id
    FROM (
        SELECT id,
               ROW_NUMBER() OVER (PARTITION BY content ORDER BY create_time DESC) AS rn
        FROM logs
    ) t
    WHERE rn > 1
) l2 ON l1.id = l2.id;

✅ Assessment point: deduplication strategy, window functions in DML, data cleaning.

10. Calculate user retention rates (next‑day and 7‑day retention)

Table: user_login(user_id, login_date) Next‑day retention SQL:

WITH first_login AS (
    SELECT user_id, MIN(login_date) AS first_day
    FROM user_login
    GROUP BY user_id
), retention AS (
    SELECT
        f.user_id,
        f.first_day,
        CASE WHEN u.user_id IS NOT NULL THEN 1 ELSE 0 END AS retained
    FROM first_login f
    LEFT JOIN user_login u
        ON f.user_id = u.user_id
        AND u.login_date = DATE_ADD(f.first_day, INTERVAL 1 DAY)
)
SELECT
    first_day,
    COUNT(*) AS new_users,
    SUM(retained) AS retained_users,
    ROUND(SUM(retained) / COUNT(*), 4) AS retention_rate
FROM retention
GROUP BY first_day
ORDER BY first_day;

✅ Assessment point: business metric modeling, date arithmetic, retention analysis.