Advanced SQL Interview Questions: Classic FAANG Problems Solved + LeetCode Top 50

These are the advanced SQL interview questions that product companies ask again and again: second highest salary, duplicates, customers who never ordered, running totals, consecutive logins and medians, each solved more than one way, followed by scenario queries in the style of Amazon, Netflix, Uber and LinkedIn, and a mapped list of the top 50 LeetCode SQL problems. For tester-focused questions, see the SQL interview questions for testers.

Classic SQL Interview Problems

Problem 1: Second Highest Salary (Asked by Amazon, Google, Meta)

-- Method 1: LIMIT + OFFSET (MySQL/PostgreSQL)
SELECT DISTINCT salary AS second_highest
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

-- Method 2: MAX with exclusion (works everywhere)
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Method 3: DENSE_RANK (also gives the Nth highest; returns no rows if there is no 2nd salary)
SELECT salary AS second_highest FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) t WHERE rnk = 2;

-- ⚠️ Edge case: if there is only 1 unique salary, LeetCode expects NULL.
-- Method 2 returns NULL; Methods 1 and 3 return no rows.
-- To return NULL with Method 1, wrap it: SELECT (SELECT DISTINCT salary ... LIMIT 1 OFFSET 1) AS second_highest;

Problem 2: Find Duplicate Emails

-- Find emails that appear more than once
SELECT email, COUNT(*) AS count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Get the duplicate ROWS (all of them)
SELECT * FROM users
WHERE email IN (
    SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1
);

-- Delete duplicates, keep one (keep lowest id)
-- (MySQL needs the extra derived table, otherwise error 1093)
DELETE FROM users
WHERE id NOT IN (
    SELECT min_id FROM (SELECT MIN(id) AS min_id FROM users GROUP BY email) keep
);

Problem 3: Customers Who Never Ordered

-- Method 1: LEFT JOIN with NULL check
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;

-- Method 2: NOT EXISTS (better with large tables)
SELECT customer_id, name FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

-- Method 3: NOT IN (avoid if orders might have NULL customer_id!)
SELECT customer_id, name FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

Problem 4: Department with Highest Average Salary

-- Find the single department with highest average salary
SELECT dept, ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY dept
ORDER BY avg_salary DESC
LIMIT 1;

-- Handle ties: departments with max average (multiple winners)
WITH dept_avg AS (
    SELECT dept, AVG(salary) AS avg_sal
    FROM employees GROUP BY dept
)
SELECT dept, avg_sal FROM dept_avg
WHERE avg_sal = (SELECT MAX(avg_sal) FROM dept_avg);

Problem 5: Running Total / Cumulative Sum (Amazon, Netflix)

-- Cumulative revenue by date
SELECT order_date, daily_revenue,
       SUM(daily_revenue) OVER (ORDER BY order_date) AS cumulative_revenue
FROM (
    SELECT DATE(order_date) AS order_date, SUM(amount) AS daily_revenue
    FROM orders GROUP BY DATE(order_date)
) daily;

-- Cumulative by department
SELECT dept, name, salary,
       SUM(salary) OVER (PARTITION BY dept ORDER BY salary) AS running_total_dept
FROM employees;

Problem 6: Consecutive Logins (Meta / Facebook classic)

-- Find users who logged in for 3 or more consecutive days
WITH login_dates AS (
    SELECT user_id, login_date,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn,
           DATE_SUB(login_date, INTERVAL ROW_NUMBER()
               OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS grp
    FROM logins
    GROUP BY user_id, login_date  -- deduplicate same-day logins
)
SELECT user_id, MIN(login_date) AS streak_start,
       MAX(login_date) AS streak_end,
       COUNT(*) AS consecutive_days
FROM login_dates
GROUP BY user_id, grp
HAVING COUNT(*) >= 3
ORDER BY user_id, streak_start;

Problem 7: Median Salary

-- MySQL: Find median salary
SELECT AVG(salary) AS median_salary
FROM (
    SELECT salary,
           ROW_NUMBER() OVER (ORDER BY salary) AS row_num,
           COUNT(*) OVER () AS total
    FROM employees
) t
WHERE row_num IN (FLOOR((total + 1) / 2), CEIL((total + 1) / 2));

-- PostgreSQL
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median
FROM employees;

Problem 8: Swap Salary Gender (LeetCode Classic)

-- Toggle sex column: m → f, f → m
UPDATE salary
SET sex = CASE WHEN sex = 'm' THEN 'f' ELSE 'm' END;

-- Alternative: using IF (MySQL)
UPDATE salary SET sex = IF(sex = 'm', 'f', 'm');

The 5 Most-Asked LeetCode SQL Problems, Solved

LC #185 — Department Top Three Salaries (Hard)

-- Problem: Find employees who are in the top 3 salary earners in their department.
-- Tied salaries count as the same rank (use DENSE_RANK).

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

LC #262 — Trips and Users (Hard)

-- Problem: Find cancellation rate for non-banned users, Oct 1-3 2013.
-- Cancellation rate = cancelled trips / total trips, rounded to 2 decimal places.

SELECT t.request_at AS Day,
       ROUND(SUM(CASE WHEN t.status != 'completed' THEN 1 ELSE 0 END)
             / COUNT(*), 2) AS "Cancellation Rate"
FROM Trips t
WHERE t.client_id NOT IN (SELECT users_id FROM Users WHERE banned = 'Yes')
  AND t.driver_id NOT IN (SELECT users_id FROM Users WHERE banned = 'Yes')
  AND t.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY t.request_at
ORDER BY t.request_at;

LC #601 — Human Traffic of Stadium (Hard)

-- Problem: Find rows where 3+ consecutive id rows each have people >= 100.

WITH valid AS (
    SELECT id, visit_date, people
    FROM Stadium WHERE people >= 100
),
grouped AS (
    SELECT id, visit_date, people,
           id - ROW_NUMBER() OVER (ORDER BY id) AS grp
    FROM valid
),
streaks AS (
    SELECT grp FROM grouped
    GROUP BY grp
    HAVING COUNT(*) >= 3
)
SELECT id, visit_date, people
FROM grouped
WHERE grp IN (SELECT grp FROM streaks)
ORDER BY visit_date;

LC #550 — Game Play Analysis IV (Retention)

-- Problem: Find fraction of players who logged in again on the day after first login.

WITH first_login AS (
    SELECT player_id, MIN(event_date) AS first_date
    FROM Activity
    GROUP BY player_id
)
SELECT ROUND(
    COUNT(DISTINCT a.player_id) / (SELECT COUNT(DISTINCT player_id) FROM Activity),
    2) AS fraction
FROM Activity a
JOIN first_login f ON a.player_id = f.player_id
WHERE a.event_date = DATE_ADD(f.first_date, INTERVAL 1 DAY);

LC #197 — Rising Temperature

-- Problem: Find IDs where temperature is higher than the previous day.

-- Method 1: Self JOIN on consecutive dates
SELECT w1.id
FROM Weather w1
JOIN Weather w2 ON w2.recordDate = DATE_SUB(w1.recordDate, INTERVAL 1 DAY)
WHERE w1.temperature > w2.temperature;

-- Method 2: LAG window function (cleaner)
SELECT id FROM (
    SELECT id,
           temperature,
           LAG(temperature) OVER (ORDER BY recordDate) AS prev_temp,
           LAG(recordDate)  OVER (ORDER BY recordDate) AS prev_date
    FROM Weather
) t
WHERE temperature > prev_temp
  AND DATEDIFF(recordDate, prev_date) = 1;  -- ensure truly consecutive days

FAANG Scenario-Based Questions

Scenario A: Amazon — Flag products needing quality review

SELECT p.product_id, p.name, p.category,
       COUNT(r.review_id)      AS total_reviews,
       ROUND(AVG(r.rating),2)  AS avg_rating
FROM products p
JOIN reviews r ON p.product_id = r.product_id
GROUP BY p.product_id, p.name, p.category
HAVING COUNT(r.review_id) > 100 AND AVG(r.rating) < 3.0
ORDER BY avg_rating ASC;

Scenario B: Swiggy — Top performers (50+ deliveries, avg < 30 min)

SELECT r.rider_id, r.name,
       COUNT(o.order_id) AS deliveries,
       ROUND(AVG(TIMESTAMPDIFF(MINUTE, o.picked_at, o.delivered_at)),2) AS avg_min
FROM riders r
JOIN orders o ON r.rider_id = o.rider_id
WHERE o.status = 'delivered'
  AND o.delivered_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY r.rider_id, r.name
HAVING COUNT(o.order_id) >= 50 AND AVG(TIMESTAMPDIFF(MINUTE,o.picked_at,o.delivered_at)) < 30
ORDER BY avg_min ASC;

Scenario C: Netflix — Users who watched Action but not Horror

SELECT DISTINCT u.user_id, u.name
FROM users u
JOIN watch_history wh ON u.user_id = wh.user_id
JOIN content c ON wh.content_id = c.content_id
WHERE c.genre = 'Action'
  AND u.user_id NOT IN (
      SELECT wh2.user_id FROM watch_history wh2
      JOIN content c2 ON wh2.content_id = c2.content_id
      WHERE c2.genre = 'Horror'
  );

Scenario D: Uber — Hours where demand exceeds supply by 2x

WITH demand AS (
    SELECT HOUR(request_time) AS hr, COUNT(*) AS requests
    FROM ride_requests WHERE DATE(request_time) = CURDATE()
    GROUP BY hr
),
supply AS (
    SELECT HOUR(available_from) AS hr, COUNT(DISTINCT driver_id) AS drivers
    FROM driver_availability WHERE DATE(available_from) = CURDATE()
    GROUP BY hr
)
SELECT d.hr,
       d.requests      AS ride_demand,
       s.drivers       AS driver_supply,
       ROUND(d.requests / NULLIF(s.drivers,0), 2) AS ratio
FROM demand d
LEFT JOIN supply s ON d.hr = s.hr
HAVING ratio >= 2
ORDER BY d.hr;

Scenario E: LinkedIn — 2nd-degree connections

-- Find people connected to MY connections (but not directly to me)
SELECT DISTINCT c2.person_b AS second_degree_connection
FROM connections c1
JOIN connections c2 ON c1.person_b = c2.person_a
WHERE c1.person_a = :my_user_id
  AND c2.person_b != :my_user_id
  AND c2.person_b NOT IN (
      SELECT person_b FROM connections WHERE person_a = :my_user_id
  );

LeetCode SQL Top 50: Mapped by Difficulty

Easy Problems (Warm-Up — Must Solve All)

Medium Problems (Core Interview Level)

Hard Problems (FAANG Senior Level)

FAQs

How do you find the second highest salary in SQL?

SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees), which returns NULL if there is no second salary; DENSE_RANK generalises to the Nth highest.

Which SQL topics do FAANG interviews focus on?

Joins, aggregation with HAVING, window functions (ranking, LAG/LEAD, running totals), CTEs, date handling, and patterns such as retention, funnels and gaps and islands.

How many LeetCode SQL problems should I practise?

The 50 listed here cover almost every pattern. Solve the easy ones quickly, then the medium ones until you can explain each approach out loud.