Classic SQL interview questions
50 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)A small set of SQL questions comes up again and again in placement interviews. Each one tests a tool from this module, and interviewers usually ask a follow-up: "what about ties?", "what if there is no such row?", "can you do it without window functions?". Knowing two solutions and their edge cases is what makes an answer strong.
Second highest and Nth highest salary
The Nth highest salary is the Nth distinct salary in descending order. If two people share the top salary, the second highest is the next amount down, not the same amount again.
Who earns the second highest salary? Two people can share a salary, so count distinct amounts, not rows.
Example 1 · Second and Nth highest salary, three ways
-- 1. MAX below the MAXSELECT MAX(salary) AS second_highestFROM employeesWHERE salary < (SELECT MAX(salary) FROM employees);
-- 2. DISTINCT + LIMIT/OFFSET: the 6th highest salarySELECT salary AS sixth_without_distinctFROM employees ORDER BY salary DESC LIMIT 1 OFFSET 5;
SELECT DISTINCT salary AS sixth_highestFROM employees ORDER BY salary DESC LIMIT 1 OFFSET 5;
-- 3. DENSE_RANK: the 6th highest salary and who earns itWITH ranked AS ( SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr FROM employees)SELECT name, salary FROM ranked WHERE dr = 6 ORDER BY name;
-- No 2nd highest in Research (it has nobody): NULL, not an empty resultSELECT (SELECT DISTINCT salary FROM employees WHERE dept_id = 5 ORDER BY salary DESC LIMIT 1 OFFSET 1) AS second_in_research;+----------------+ | second_highest | +----------------+ | 180000.00 | +----------------+ +------------------------+ | sixth_without_distinct | +------------------------+ | 120000.00 | +------------------------+ +---------------+ | sixth_highest | +---------------+ | 95000.00 | +---------------+ +-------------+----------+ | name | salary | +-------------+----------+ | Suresh Babu | 95000.00 | +-------------+----------+ +--------------------+ | second_in_research | +--------------------+ | NULL | +--------------------+
What this code does
- Method 1 finds the largest salary below 250000.00, which is 180000.00. It is the shortest answer for "second highest" only.
- Without
DISTINCT, the two 120000.00 salaries take two places, soOFFSET 5lands on 120000.00. WithDISTINCTthe sixth distinct salary is correctly 95000.00, andDENSE_RANKagrees and names Suresh Babu. - The last query wraps the search in a scalar subquery, so a missing answer comes back as
NULL.
Three standard answers:
- Subquery with MAX:
MAX(salary) WHERE salary < (SELECT MAX(salary) ...). Clear for the 2nd highest, but it does not extend to N. - DISTINCT with LIMIT and OFFSET:
SELECT DISTINCT salary ... ORDER BY salary DESC LIMIT 1 OFFSET N-1. WithoutDISTINCT, duplicate salaries shift the offset and give a wrong answer. - DENSE_RANK: rank in a CTE and keep
rank = N. It also returns the names of everyone at that salary.
The usual follow-up is "what if there is no Nth salary?": wrapping the query as a scalar subquery, SELECT (SELECT DISTINCT salary ... LIMIT 1 OFFSET 1) AS second_highest, returns NULL instead of an empty result, which is what most interviewers expect.
Predict the output
Predict the Nth highest
Salaries in descending order: 250000, 180000, 150000, 140000, 120000 (twice), 95000, 90000.
Round 1 of 2
Top N per group
Top N per group asks for the best few rows inside every group, such as the two highest-paid people in each department. A window function with PARTITION BY ranks each group on its own.
Top N per group asks for the best few rows inside every group: the two highest-paid people in each department, the three best-selling products per category.
Example 2 · Top 2 earners in each department, two ways
-- With a window functionWITH ranked AS ( SELECT d.dept_name, e.name, e.salary, DENSE_RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS dr FROM employees e JOIN departments d ON d.dept_id = e.dept_id)SELECT dept_name, name, salaryFROM rankedWHERE dr <= 2ORDER BY dept_name, salary DESC, name;
-- Without window functions: fewer than 2 colleagues earn moreSELECT d.dept_name, e.name, e.salaryFROM employees eJOIN departments d ON d.dept_id = e.dept_idWHERE (SELECT COUNT(*) FROM employees x WHERE x.dept_id = e.dept_id AND x.salary > e.salary) < 2ORDER BY d.dept_name, e.salary DESC, e.name;+-------------+---------------+-----------+ | dept_name | name | salary | +-------------+---------------+-----------+ | Engineering | Vikram Rao | 250000.00 | | Engineering | Anita Desai | 180000.00 | | Finance | Gita Reddy | 140000.00 | | Finance | Manoj Pillai | 75000.00 | | HR | Lakshmi Menon | 90000.00 | | HR | Rohan Das | 60000.00 | | Sales | Neha Joshi | 150000.00 | | Sales | Amit Shah | 85000.00 | | Sales | Farah Ali | 85000.00 | +-------------+---------------+-----------+ +-------------+---------------+-----------+ | dept_name | name | salary | +-------------+---------------+-----------+ | Engineering | Vikram Rao | 250000.00 | | Engineering | Anita Desai | 180000.00 | | Finance | Gita Reddy | 140000.00 | | Finance | Manoj Pillai | 75000.00 | | HR | Lakshmi Menon | 90000.00 | | HR | Rohan Das | 60000.00 | | Sales | Neha Joshi | 150000.00 | | Sales | Amit Shah | 85000.00 | | Sales | Farah Ali | 85000.00 | +-------------+---------------+-----------+
What this code does
DENSE_RANKwithPARTITION BY e.dept_idranks inside each department;dr <= 2keeps the top two distinct salaries. In Sales, Amit Shah and Farah Ali tie for 2nd, so both appear.- The correlated version counts, for each employee, the colleagues in the same department who earn more. Here it returns the same rows, and it also runs on MySQL 5.7.
- In an interview, say which tie rule you chose:
ROW_NUMBERfor exactly two people,RANK/DENSE_RANKto include ties.
GROUP BY with MAX gives only one value per group and loses the other columns, so it cannot answer this.
The modern answer ranks with PARTITION BY in a CTE and keeps rank <= N. Pick the function by the tie rule: ROW_NUMBER for exactly N rows, RANK or DENSE_RANK when tied rows should all be included. The classic answer without window functions (needed on MySQL 5.7) is a correlated count: keep an employee when fewer than N colleagues in the same department earn more, WHERE (SELECT COUNT(*) FROM employees x WHERE x.dept_id = e.dept_id AND x.salary > e.salary) < 2. It treats ties like RANK.
Order of execution
Put the top-2 query in order
Rank inside each department first, then keep only the top two.
Finding and deleting duplicates
Duplicate rows are rows that repeat the same business value, such as one email address signed up three times, even though their ids differ. Find them with GROUP BY email HAVING COUNT(*) > 1, which also shows how many copies each value has.
Example 3 · Find duplicate sign-ups, then delete the extra copies
DROP TABLE IF EXISTS signups;
CREATE TABLE signups ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL, email VARCHAR(60) NOT NULL, signed_on DATE NOT NULL);
-- College fest newsletter: some students signed up more than onceINSERT INTO signups (name, email, signed_on) VALUES ('Riya', 'riya@example.com', '2025-09-01'), ('Arun', 'arun@example.com', '2025-09-01'), ('Riya S', 'riya@example.com', '2025-09-03'), ('Meera', 'meera@example.com', '2025-09-04'), ('Arun K', 'arun@example.com', '2025-09-05'), ('Riya', 'riya@example.com', '2025-09-06'), ('Tom', 'tom@example.com', '2025-09-06');
-- Find: which emails repeat, and which id to keepSELECT email, COUNT(*) AS copies, MIN(id) AS keep_idFROM signupsGROUP BY emailHAVING COUNT(*) > 1ORDER BY email;
-- Delete every copy after the first (through a derived table)DELETE FROM signupsWHERE id IN ( SELECT id FROM ( SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn FROM signups ) AS numbered WHERE rn > 1);
SELECT id, name, email FROM signups ORDER BY id;+------------------+--------+---------+ | email | copies | keep_id | +------------------+--------+---------+ | arun@example.com | 2 | 2 | | riya@example.com | 3 | 1 | +------------------+--------+---------+ +----+-------+-------------------+ | id | name | email | +----+-------+-------------------+ | 1 | Riya | riya@example.com | | 2 | Arun | arun@example.com | | 4 | Meera | meera@example.com | | 7 | Tom | tom@example.com | +----+-------+-------------------+
What this code does
GROUP BY email HAVING COUNT(*) > 1lists the two emails that repeat; Riya's address appears three times.ROW_NUMBERnumbers the copies of each email by id. Rows numbered 2 and above (ids 3, 5 and 6) are deleted, so the earliest sign-up of each email stays.- The extra
SELECT id FROM (...) AS numberedlayer is the derived-table workaround for error 1093. Next step in real life:ALTER TABLE signups ADD UNIQUE (email);.
In MySQL a DELETE cannot read its own table in a plain subquery (error 1093), so the ids of the extra copies must be read through a derived table.
The older MySQL way is a self-join delete: DELETE s1 FROM signups s1 JOIN signups s2 ON s1.email = s2.email AND s1.id > s2.id; removes every row that has a twin with a smaller id. Afterwards, add a UNIQUE constraint on the column so duplicates cannot come back.
Fix the error
Fix the duplicate finder
The query should list emails that were signed up more than once.
A condition on COUNT(*) must filter the groups.
Error
ERROR 1064 (42000): You have an error in your SQL syntax near 'WHERE COUNT(*) > 1'
SELECT version first and check which ids will go, and practise on a copy of the data. A wrong DELETE cannot be undone with a query.Consecutive days and gaps
Gaps and islands is the problem of finding runs of consecutive values, the islands, and the missing values between them, the gaps. Streaks and missing numbers are the usual questions.
Streak questions ("longest run of consecutive login days") are called gaps and islands: each run of consecutive values is an island, and the missing values between them are gaps. The trick is to subtract the row's position from its date:
Example 4 · Coding streaks: consecutive days
DROP TABLE IF EXISTS coding_log;
CREATE TABLE coding_log ( learner VARCHAR(20) NOT NULL, day DATE NOT NULL, PRIMARY KEY (learner, day));
INSERT INTO coding_log VALUES ('Aman', '2025-09-01'), ('Aman', '2025-09-02'), ('Aman', '2025-09-03'), ('Aman', '2025-09-05'), ('Aman', '2025-09-06'), ('Aman', '2025-09-09'), ('Bela', '2025-09-02'), ('Bela', '2025-09-03'), ('Bela', '2025-09-04'), ('Bela', '2025-09-05'), ('Bela', '2025-09-08');
-- See the trick: day minus row number stays the same inside a streakSELECT day, ROW_NUMBER() OVER (ORDER BY day) AS rn, DATE_SUB(day, INTERVAL ROW_NUMBER() OVER (ORDER BY day) DAY) AS grpFROM coding_logWHERE learner = 'Aman'ORDER BY day;
-- Every streak of every learnerWITH numbered AS ( SELECT learner, day, DATE_SUB(day, INTERVAL ROW_NUMBER() OVER (PARTITION BY learner ORDER BY day) DAY) AS grp FROM coding_log)SELECT learner, MIN(day) AS streak_start, MAX(day) AS streak_end, COUNT(*) AS daysFROM numberedGROUP BY learner, grpORDER BY learner, streak_start;+------------+----+------------+ | day | rn | grp | +------------+----+------------+ | 2025-09-01 | 1 | 2025-08-31 | | 2025-09-02 | 2 | 2025-08-31 | | 2025-09-03 | 3 | 2025-08-31 | | 2025-09-05 | 4 | 2025-09-01 | | 2025-09-06 | 5 | 2025-09-01 | | 2025-09-09 | 6 | 2025-09-03 | +------------+----+------------+ +---------+--------------+------------+------+ | learner | streak_start | streak_end | days | +---------+--------------+------------+------+ | Aman | 2025-09-01 | 2025-09-03 | 3 | | Aman | 2025-09-05 | 2025-09-06 | 2 | | Aman | 2025-09-09 | 2025-09-09 | 1 | | Bela | 2025-09-02 | 2025-09-05 | 4 | | Bela | 2025-09-08 | 2025-09-08 | 1 | +---------+--------------+------------+------+
What this code does
- In the first result, 1, 2 and 3 September all give
grp2025-08-31: the date and the row number rise together. After the break on 4 September,grpjumps to a new value. - Grouping by
learner, grpturns each island into one row with its start, end and length. Bela's longest streak is 4 days, from 2 to 5 September. - The primary key
(learner, day)matters: a duplicate day would break the trick, so with messy data useDISTINCTdays first.
Example 5 · Missing bill numbers
DROP TABLE IF EXISTS shop_bills;
CREATE TABLE shop_bills ( bill_no INT PRIMARY KEY, amount INT NOT NULL);
INSERT INTO shop_bills VALUES (1001, 80), (1002, 45), (1003, 120), (1006, 60), (1007, 95), (1010, 30);
WITH with_next AS ( SELECT bill_no, LEAD(bill_no) OVER (ORDER BY bill_no) AS next_bill FROM shop_bills)SELECT bill_no + 1 AS gap_starts, next_bill - 1 AS gap_ends, next_bill - bill_no - 1 AS missingFROM with_nextWHERE next_bill - bill_no > 1ORDER BY gap_starts;+------------+----------+---------+ | gap_starts | gap_ends | missing | +------------+----------+---------+ | 1004 | 1005 | 2 | | 1008 | 1009 | 2 | +------------+----------+---------+
What this code does
LEADputs the next bill number beside each bill. After 1003 the next is 1006, so 1004 and 1005 are missing.- The last bill (1010) has
next_billNULL, andNULL > 1is not true, so it never reports a false gap. - An auditor uses exactly this query to spot deleted or skipped bills.
DATE_SUB(day, INTERVAL ROW_NUMBER() OVER (ORDER BY day) DAY). Inside one run both the date and the number grow by 1, so the difference stays the same; a break changes it. Group by that difference to get each streak's start, end and length.
Gap questions ("which bill numbers are missing?") compare each row with the next one using LEAD. When the next number is more than 1 higher, the numbers in between are missing. Both patterns work for any sequence: dates, bill numbers, roll numbers or seat numbers.
Next step
Check your understanding
12 quick questions