StealthCoder

LeetCode SQL Interview Questions Solved in MySQL

2025-08-12

SQL on LeetCode is very pattern driven. If you learn a handful of templates for joins, window functions, filtering, and aggregation, you can solve most questions quickly. Below are clean MySQL solutions to common interview tasks with short notes you can repeat out loud.

I use sample schemas similar to LeetCode. You can paste each snippet into a MySQL client and adapt names if your table differs.

1) Second Highest Salary

Goal: return the second distinct highest salary or NULL if it does not exist.

SELECT
  (
    SELECT DISTINCT salary
    FROM Employee
    ORDER BY salary DESC
    LIMIT 1 OFFSET 1
  ) AS SecondHighestSalary;

Why this works: LIMIT 1 OFFSET 1 skips the top distinct salary. If the table has fewer than two distinct values, the subquery returns NULL.

Window alternative:

SELECT MAX(salary) AS SecondHighestSalary
FROM (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM Employee
) t
WHERE rnk = 2;

2) Nth Highest Salary (parameterized)

-- set @N := 3; -- example
SET @N := 3;

SELECT (
  SELECT DISTINCT salary
  FROM Employee
  ORDER BY salary DESC
  LIMIT 1 OFFSET @N - 1
) AS NthHighestSalary;

If your interviewer allows window functions:

SET @N := 3;

WITH ranked AS (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM Employee
)
SELECT salary AS NthHighestSalary
FROM ranked
WHERE rnk = @N;

3) Department Highest Salary

Goal: for each department, return the employee with the top salary. If ties exist, return all.

SELECT d.name AS Department, e.name AS Employee, e.salary
FROM Employee e
JOIN Department d ON e.departmentId = d.id
JOIN (
  SELECT departmentId, MAX(salary) AS max_sal
  FROM Employee
  GROUP BY departmentId
) m ON m.departmentId = e.departmentId AND m.max_sal = e.salary
ORDER BY d.name, e.name;

Why: precompute each department max, then join back to select matching rows.

4) Customers Who Never Order

SELECT c.name AS Customers
FROM Customers c
LEFT JOIN Orders o ON o.customerId = c.id
WHERE o.id IS NULL
ORDER BY c.name;

Pattern: left join then filter NULL on the right table to find non matches.

5) Duplicate Emails

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

Tip: use HAVING on aggregates. Do not try WHERE COUNT(*) > 1.

6) Rising Temperature

Goal: find days where temperature is higher than the previous day.

SELECT w1.id
FROM Weather w1
JOIN Weather w0
  ON DATE_SUB(w1.recordDate, INTERVAL 1 DAY) = w0.recordDate
WHERE w1.temperature > w0.temperature
ORDER BY w1.id;

Window alternative:

SELECT id
FROM (
  SELECT id,
         temperature,
         LAG(temperature) OVER (ORDER BY recordDate) AS prev_temp
  FROM Weather
) t
WHERE temperature > prev_temp;

7) Activity Rank per User

Goal: for each user, order activities by event time and keep only the first row.

SELECT user_id, activity, event_time
FROM (
  SELECT user_id, activity, event_time,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time) AS rn
  FROM Activity
) t
WHERE rn = 1;

Speaking point: partition by user, order by time, pick rank 1.

8) Employees Earning More Than Their Manager

SELECT e.name AS Employee
FROM Employee e
JOIN Employee m ON m.id = e.managerId
WHERE e.salary > m.salary
ORDER BY e.name;

Pattern: self join table to itself.

9) Friend Requests I: Overall Acceptance Rate

Tables: FriendRequest(sender_id, send_to_id, request_date) and RequestAccepted(sender_id, receive_id, accept_date).

SELECT
  ROUND(
    IFNULL(
      (SELECT COUNT(*) FROM RequestAccepted) /
      NULLIF((SELECT COUNT(*) FROM FriendRequest), 0),
      0
    ),
    2
  ) AS accept_rate;

Notes: NULLIF avoids division by zero. IFNULL handles no data case. Round to two decimals if asked.

10) Top 3 Salaries per Department

SELECT d.name AS Department, e.name AS Employee, e.salary
FROM (
  SELECT *,
         DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) AS rnk
  FROM Employee
) e
JOIN Department d ON d.id = e.departmentId
WHERE e.rnk <= 3
ORDER BY d.name, e.salary DESC, e.name;

Pattern: window rank inside a subquery, then filter by rank.

11) Consecutive Numbers

Goal: find numbers that appear at least three times in a row.

SELECT DISTINCT l1.num AS ConsecutiveNums
FROM Logs l1
JOIN Logs l2 ON l2.id = l1.id + 1 AND l2.num = l1.num
JOIN Logs l3 ON l3.id = l1.id + 2 AND l3.num = l1.num;

Why: align rows by offset using self joins.

12) Trips and Users: Cancellation Rate

Tables: Trips(id, client_id, driver_id, status, request_at) and Users(users_id, banned, role).

SELECT
  t.request_at AS Day,
  ROUND(
    AVG(CASE WHEN t.status IN ('cancelled_by_client', 'cancelled_by_driver') THEN 1 ELSE 0 END),
    2
  ) AS CancellationRate
FROM Trips t
JOIN Users c ON c.users_id = t.client_id AND c.banned = 'No'
JOIN Users d ON d.users_id = t.driver_id AND d.banned = 'No'
GROUP BY t.request_at
ORDER BY t.request_at;

Trick: average of a 0 or 1 expression yields the rate.

13) Ranking Scores

Goal: dense ranks for scores.

SELECT score,
       DENSE_RANK() OVER (ORDER BY score DESC) AS `rank`
FROM Scores
ORDER BY score DESC;

14) Sales Person Without Orders for Company X

SELECT s.name
FROM SalesPerson s
WHERE NOT EXISTS (
  SELECT 1
  FROM Orders o
  JOIN Company c ON c.com_id = o.com_id
  WHERE o.sales_id = s.sales_id
    AND c.name = 'RED'
);

Pattern: NOT EXISTS for anti join logic.

15) Employees Project Allocation: Longest Streak

Goal: for each employee, find the longest run of consecutive days worked.

WITH t AS (
  SELECT employee_id,
         work_date,
         DATE_SUB(work_date, INTERVAL ROW_NUMBER() OVER (
           PARTITION BY employee_id ORDER BY work_date
         ) DAY) AS grp
  FROM Attendance
)
SELECT employee_id,
       MAX(COUNT(*)) OVER (PARTITION BY employee_id) AS longest_streak
FROM t
GROUP BY employee_id, grp;

Idea: subtract the row number from the date to create equal keys for consecutive runs.

16) Average Time between Visits

Goal: compute average seconds between consecutive events per user.

SELECT user_id,
       ROUND(AVG(TIMESTAMPDIFF(SECOND, prev_time, event_time)), 2) AS avg_secs_between
FROM (
  SELECT user_id,
         event_time,
         LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_time
  FROM Events
) x
WHERE prev_time IS NOT NULL
GROUP BY user_id;

17) Rolling 7 Day Active Users

SELECT d.dt,
       COUNT(DISTINCT a.user_id) AS dau_7d
FROM Dates d
LEFT JOIN Activity a
  ON a.activity_date BETWEEN DATE_SUB(d.dt, INTERVAL 6 DAY) AND d.dt
GROUP BY d.dt
ORDER BY d.dt;

Interview note: if Dates table is not present, generate dates on the fly or constrain to dates present in Activity.

18) Percentile by Category

Goal: for each category, tag values at or above the 90th percentile.

WITH ranked AS (
  SELECT category, value,
         PERCENT_RANK() OVER (PARTITION BY category ORDER BY value) AS pr
  FROM Metrics
)
SELECT category, value,
       CASE WHEN pr >= 0.9 THEN 'top_decile' ELSE 'other' END AS bucket
FROM ranked;

MySQL supports PERCENT_RANK from 8.0 onward.

19) Running Total per User

SELECT user_id,
       event_time,
       amount,
       SUM(amount) OVER (PARTITION BY user_id ORDER BY event_time
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM Payments
ORDER BY user_id, event_time;

20) Windowed Top N with Ties

Goal: top 2 orders per customer by amount with ties included.

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

Common MySQL pitfalls in interviews

  1. Using WHERE to filter aggregates. Use HAVING.
  2. Forgetting DISTINCT when a question demands unique values.
  3. Joining without a key and creating duplicates that inflate counts.
  4. Using RANK when you wanted DENSE_RANK. Know the difference.
  5. Division by zero in rates. Protect with NULLIF and wrap with IFNULL.
  6. Relying on implicit grouping behavior. Always list all non aggregated columns in GROUP BY for clarity.
  7. Sorting after LIMIT with no ORDER BY. MySQL will not guarantee order unless you specify it.

Quick study checklist

  • Left join anti patterns for “never did X”
  • Window functions for rank, lag, lead, cumulative sum
  • Self joins for previous day or consecutive rows
  • Subqueries with IN, EXISTS, NOT EXISTS
  • Aggregation with GROUP BY and HAVING
  • Date arithmetic with DATE_SUB, TIMESTAMPDIFF

Work through these patterns and most SQL interview problems will feel familiar.

If you want a helper while practicing that can overlay a clean solution and short explanation when you are stuck, you can use a lightweight tool that captures the prompt and shows an outline you can study and explain back. It keeps you focused on patterns instead of spinning your wheels.

Try StealthCoder when you want a quick solution outline for SQL prompts: https://stealthcoder.app