SQL Interview Questions: 10 Patterns That Cover Most Rounds

Master SQL interview questions with 10 reusable query patterns: joins, Nth highest salary, window functions, top-N per group, NULL traps and more.

By InternHack Team · 2026-09-26 · 7 min read · Interview Prep

Most SQL interview questions are variations of a small set of patterns. If you learn to recognise the pattern, the exact wording of the question matters much less. This guide is for students and freshers preparing for internship, campus and off-campus rounds. It covers ten patterns with working queries on one small schema, written in standard SQL that runs on PostgreSQL (with notes where MySQL differs).

The practice schema

All examples use three tables. Keep this in your head, and the queries will be easy to follow.

Notes: employees.manager_id points to another row in employees. A new employee may have dept_id as NULL. orders.status is a value such as 'PAID', 'PENDING' or 'CANCELLED'. To try every query below, use the SQL playground and create these tables with a few rows of your own data.

Pattern 1: Filtering and aggregation with GROUP BY and HAVING

The idea: WHERE filters rows before grouping. HAVING filters groups after aggregation. Interviewers love asking about the difference.

Question: List departments with more than 5 employees and an average salary above 60000.

SELECT dept_id,
       COUNT(*)      AS headcount,
       AVG(salary)   AS avg_salary
FROM employees
WHERE dept_id IS NOT NULL
GROUP BY dept_id
HAVING COUNT(*) > 5
   AND AVG(salary) > 60000;

Remember the logical order of execution: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. That is why you cannot use a SELECT alias inside WHERE (though PostgreSQL allows aliases in ORDER BY). Any non-aggregated column in SELECT must appear in GROUP BY.

Pattern 2: Joins (inner, left, self, and anti-join)

The idea: Choose the join based on what should happen to unmatched rows.

Inner join: employee names with department names.

SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON d.dept_id = e.dept_id;

Employees with a NULL dept_id disappear, because the inner join only keeps matches.

Left join: all departments with their employee count, including empty ones.

SELECT d.dept_name, COUNT(e.emp_id) AS headcount
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_name;

Use COUNT(e.emp_id), not COUNT(). With COUNT(), an empty department would show 1 because the row with NULLs still counts.

Self join: each employee with their manager's name.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.emp_id = e.manager_id;

Anti-join: employees who have never placed an order. Two safe ways:

-- Option A: LEFT JOIN and check for no match
SELECT e.emp_id, e.name
FROM employees e
LEFT JOIN orders o ON o.emp_id = e.emp_id
WHERE o.order_id IS NULL;

-- Option B: NOT EXISTS
SELECT e.emp_id, e.name
FROM employees e
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.emp_id = e.emp_id
);

Prefer NOT EXISTS over NOT IN when the subquery column can contain NULLs (see Pattern 10).

Pattern 3: Nth highest salary

This is the most repeated SQL question in placements. Know at least three ways.

Second highest salary, using a subquery:

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Nth highest with LIMIT and OFFSET (here N = 3):

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2;

DISTINCT matters: without it, two people with the top salary would occupy positions 1 and 2. If fewer than N distinct salaries exist, this returns no rows.

Nth highest with DENSE_RANK (the most general answer):

SELECT salary
FROM (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk = 3
LIMIT 1;

Choosing DENSE_RANK treats tied salaries as one rank, so "3rd highest" means the third distinct value. Say this out loud in the interview; it shows you thought about ties.

Pattern 4: Finding duplicates and removing them

Find duplicate emails. Suppose a users(email) table for this one example:

SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Delete duplicates but keep the row with the smallest id (PostgreSQL):

DELETE FROM users
WHERE id IN (
  SELECT id
  FROM (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM users
  ) t
  WHERE rn > 1
);

The window function numbers each duplicate group, and we delete everything except row number 1. In MySQL, deleting from a table you also read in a subquery can raise an error, so use a derived table or a self-join delete.

Pattern 5: Window functions basics (ROW_NUMBER, RANK, DENSE_RANK)

The idea: A window function computes a value across related rows without collapsing them like GROUP BY does. The syntax is function() OVER (PARTITION BY ... ORDER BY ...).

The three ranking functions differ only on ties. For salaries 90, 80, 80, 70:

ROW_NUMBER always gives unique numbers, RANK leaves gaps after ties, and DENSE_RANK has no gaps.

Rank employees by salary within each department:

SELECT name, dept_id, salary,
       RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dept_rank
FROM employees;

Pattern 6: Running totals and LAG/LEAD

Running total of order amounts by date (for paid orders):

SELECT order_date,
       amount,
       SUM(amount) OVER (
         ORDER BY order_date, order_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM orders
WHERE status = 'PAID';

Add a tiebreaker such as order_id in the ORDER BY. Without an explicit frame, the default treats rows with the same date as peers, which can give surprising totals.

Compare each day's sales with the previous day using LAG:

SELECT order_date,
       daily_total,
       daily_total - LAG(daily_total) OVER (ORDER BY order_date) AS change_from_prev
FROM (
  SELECT order_date, SUM(amount) AS daily_total
  FROM orders
  WHERE status = 'PAID'
  GROUP BY order_date
) d;

LAG(col, n, default) looks back n rows; LEAD looks forward. The first row has no previous value, so LAG returns NULL unless you pass a default.

Pattern 7: Top N per group

The top 2 highest-paid employees in each department:

SELECT dept_id, name, salary
FROM (
  SELECT dept_id, name, salary,
         DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
  FROM employees
  WHERE dept_id IS NOT NULL
) t
WHERE rnk <= 2;

Use ROW_NUMBER if you want exactly two rows per department and do not care about ties; use DENSE_RANK or RANK if tied employees should all be included. You cannot filter on a window function in the same SELECT's WHERE, which is why the ranking sits in a subquery (or CTE).

The same pattern answers "latest order per customer":

SELECT customer_id, order_id, order_date, amount
FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC, order_id DESC) AS rn
  FROM orders o
) t
WHERE rn = 1;

Pattern 8: Date bucketing

The idea: Truncate a date to a period (day, week, month) and group by it.

Monthly revenue from paid orders (PostgreSQL):

SELECT DATE_TRUNC('month', order_date) AS month,
       SUM(amount)                     AS revenue,
       COUNT(*)                        AS orders_count
FROM orders
WHERE status = 'PAID'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

In MySQL, use DATE_FORMAT(order_date, '%Y-%m') instead of DATE_TRUNC. For a date range, use a half-open interval, which works correctly even when the column holds timestamps:

SELECT COUNT(*)
FROM orders
WHERE order_date >= DATE '2026-01-01'
  AND order_date <  DATE '2026-02-01';

Avoid BETWEEN '2026-01-01' AND '2026-01-31' on timestamp columns, as it misses everything on January 31 after midnight.

Pattern 9: Gaps and islands, and pivoting with CASE

Gaps and islands (light version)

The idea: Find consecutive runs in a sequence. The classic trick is to subtract a row number from the value; consecutive values give a constant result.

Find runs of consecutive order dates for one customer's activity (assume one row per distinct date, in a table activity(customer_id, activity_date)):

SELECT customer_id,
       MIN(activity_date) AS streak_start,
       MAX(activity_date) AS streak_end,
       COUNT(*)           AS streak_days
FROM (
  SELECT customer_id,
         activity_date,
         activity_date - CAST(ROW_NUMBER() OVER (
           PARTITION BY customer_id ORDER BY activity_date
         ) AS INT) AS grp
  FROM activity
) t
GROUP BY customer_id, grp
ORDER BY customer_id, streak_start;

In PostgreSQL, subtracting an integer from a DATE gives a date. Consecutive days share the same grp, so grouping by it forms one island per streak.

Pivoting with CASE

Order counts by status as columns, per sales rep:

SELECT emp_id,
       SUM(CASE WHEN status = 'PAID'      THEN 1 ELSE 0 END) AS paid,
       SUM(CASE WHEN status = 'PENDING'   THEN 1 ELSE 0 END) AS pending,
       SUM(CASE WHEN status = 'CANCELLED' THEN 1 ELSE 0 END) AS cancelled
FROM orders
GROUP BY emp_id;

This conditional aggregation works on every database and is what interviewers usually expect for "turn rows into columns". It also solves questions such as "percentage of cancelled orders":

SELECT 100.0 * SUM(CASE WHEN status = 'CANCELLED' THEN 1 ELSE 0 END) / COUNT(*) AS cancel_pct
FROM orders;

Pattern 10: NULL pitfalls

NULL means "unknown", and comparing anything to it gives unknown, not true or false. These are the traps that cost marks.

• Use IS NULL, never = NULL. WHERE dept_id = NULL returns no rows.
• NOT IN with NULLs returns nothing. If the subquery contains even one NULL, x NOT IN (...) can never be true.

-- Risky if orders.emp_id can be NULL: may return zero rows
SELECT name FROM employees
WHERE emp_id NOT IN (SELECT emp_id FROM orders);

-- Safe
SELECT name FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.emp_id = e.emp_id);

• Aggregates ignore NULLs. AVG(salary) skips NULL salaries, and COUNT(col) counts only non-NULL values, while COUNT(*) counts rows.
• Use COALESCE for defaults. COALESCE(dept_id, 0) replaces NULL with 0.
• NULLs in ordering. PostgreSQL puts NULLs last in ascending order and first in descending; use NULLS FIRST or NULLS LAST to be explicit.
• CASE without ELSE returns NULL for unmatched rows, which affects sums and counts.

How to practise these patterns

1. Type each query yourself instead of copying. Change the question slightly and rewrite.
2. Predict the output before running it; mismatches teach you the most.
3. For each problem, ask "which pattern is this?" before writing any code.
4. Practise explaining tie handling and NULL behaviour aloud, since interviewers often ask follow-ups.

Use the free SQL playground to run queries, work through the SQL course for fundamentals, and revise concepts such as indexes, normalisation and transactions in the SQL and database interview module. If you are combining SQL with data structures and algorithms in the same preparation cycle, the 90-day internship interview plan shows how to divide your time, and the DSA section covers the coding side.

FAQ

What SQL topics are asked most in interviews?

The most common are joins, GROUP BY with HAVING, subqueries, Nth highest salary, finding duplicates, window functions such as ROW_NUMBER and RANK, and NULL handling. For database roles you may also get questions about indexes, normalisation and transactions.

How do I find the Nth highest salary in SQL?

The most general method is a window function: rank distinct salaries with DENSE_RANK in descending order and select the row where the rank equals N. Alternatives are LIMIT with OFFSET on distinct salaries, or a correlated subquery. Mention how ties are handled.

What is the difference between RANK, DENSE_RANK and ROW_NUMBER?

ROW_NUMBER assigns a unique sequence to every row, even when values tie. RANK gives tied rows the same number and skips the next numbers, while DENSE_RANK gives tied rows the same number without skipping. Choose based on how ties should be treated in the question.

What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping and cannot use aggregate functions. HAVING filters groups after aggregation, so it can use functions like COUNT and AVG. A query can use both together.

Why does NOT IN return no rows when the list has a NULL?

Because comparing a value with NULL yields unknown, and NOT IN requires the comparison to be true for every item in the list. One NULL makes the whole condition unknown for every row. Use NOT EXISTS or filter out NULLs in the subquery.

Do interviewers expect MySQL or PostgreSQL syntax?

Usually they accept standard SQL and care more about your logic. Small differences such as DATE_TRUNC versus DATE_FORMAT, or LIMIT versus TOP in SQL Server, are fine if you mention them. If a company names a specific database, practise its dialect in advance.