Interview tasks — SQL · QA Interview Hub

Task: find the second highest salary.

The first clarification worth stating: "if two people have the maximum salary, is the second highest the same amount or the next one?" Usually the next unique amount is meant.

-- variant 1: a subquery, returns NULL if there is no second
SELECT MAX(salary) AS second_max
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- variant 2: DISTINCT + OFFSET
SELECT DISTINCT salary FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

-- variant 3: DENSE_RANK, easy to generalise to the Nth
WITH r AS (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
  FROM employees
)
SELECT DISTINCT salary FROM r WHERE dr = 2;

In an interview: First I clarify whether to take repeats of the maximum salary into account. The simplest solution is the maximum salary among those smaller than the overall maximum. For the Nth highest DENSE_RANK is more convenient. And the variant with LIMIT and OFFSET without DISTINCT gives a wrong answer if the maximum salary repeats.

Task: find duplicated emails.

Group by email and keep the groups with more than one row. Case and spaces should be taken into account: Anna@test.com and anna@test.com may also be a duplicate.

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

-- taking case and spaces into account
SELECT LOWER(TRIM(email)) AS email_norm, COUNT(*) AS cnt
FROM users
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1;

In an interview: I group by email and keep the groups with a count greater than one through HAVING. And as QA I also normalise the values with LOWER and TRIM, because duplicates that differ in case or a space are a typical bug when the uniqueness check on the backend compares strings literally.

Task: find users who have no accounts at all.

An anti-join or NOT EXISTS. It is worth mentioning why not NOT IN.

SELECT u.id, u.email
FROM users u
LEFT JOIN accounts a ON a.user_id = u.id
WHERE a.id IS NULL;

In an interview: I do a LEFT JOIN of users with accounts and take those whose account key is NULL. The alternative is NOT EXISTS. And I don't use NOT IN here: if a NULL turns up in the subquery, it returns zero rows.

Task: the top 3 users by the sum of completed deposits.

Three tables, a filter, grouping, sorting and limiting. It is worth saying what to do with equal sums in third place.

SELECT u.id, u.email, SUM(t.amount) AS total_deposits
FROM users u
JOIN accounts a     ON a.user_id = u.id
JOIN transactions t ON t.account_id = a.id
WHERE t.type = 'deposit' AND t.status = 'completed'
GROUP BY u.id, u.email
ORDER BY total_deposits DESC
LIMIT 3;

-- if with equal sums you need to show everyone: RANK() and rnk <= 3

In an interview: I join users, accounts and transactions, filter completed deposits in WHERE, group by user, sort by the sum and take three. I clarify what to do with equal sums at the boundary: LIMIT will cut arbitrarily, and if everyone with equal sums must be shown, I use RANK.

Task: the number and sum of transactions by month.

Grouping by a date truncated to the month. DATE_TRUNC is PostgreSQL; in MySQL you use DATE_FORMAT(created_at, '%Y-%m').

SELECT DATE_TRUNC('month', created_at) AS month,
       COUNT(*)    AS tx_count,
       SUM(amount) AS volume
FROM transactions
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

In an interview: I truncate the date to the month with DATE_TRUNC, group by it and count the number and the sum. It is worth remembering that months without transactions won't get into the result: if they are needed with zeros, you have to generate a list of months with generate_series and join with a LEFT JOIN.

Task: employees who earn more than their manager.

A classic self join.

SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE e.salary > m.salary;

In an interview: This is a self join: I join the employees table with itself, where the manager is another row by manager_id, and compare salaries. An INNER JOIN fits here, because employees without a manager can't earn more than them anyway.

Task: remove duplicates, leaving one record each.

Number the rows in each group of duplicates and delete all except the first. Before the DELETE: a SELECT of the same rows and a transaction, without fail.

BEGIN;

-- what will be deleted: all except the oldest record of each email
WITH ranked AS (
  SELECT id, ROW_NUMBER() OVER (PARTITION BY LOWER(email) ORDER BY created_at, id) AS rn
  FROM users
)
SELECT id FROM ranked WHERE rn > 1;

-- deletion
WITH ranked AS (
  SELECT id, ROW_NUMBER() OVER (PARTITION BY LOWER(email) ORDER BY created_at, id) AS rn
  FROM users
)
DELETE FROM users WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

COMMIT;   -- or ROLLBACK

In an interview: I number the rows in each group of duplicates with ROW_NUMBER and PARTITION BY and delete everything with a number greater than one, keeping the oldest record. First I run a SELECT of those same rows, and I do everything in a transaction. And I clarify whether the duplicates have child records, otherwise foreign keys won't let the delete happen or the related data will vanish through a cascade.

Task: the percentage of failed transactions for each type.

Conditional aggregation and care with integer division.

SELECT type,
       COUNT(*) AS total,
       COUNT(*) FILTER (WHERE status = 'failed') AS failed,
       ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'failed') / COUNT(*), 2) AS failed_pct
FROM transactions
GROUP BY type
ORDER BY failed_pct DESC;

-- without FILTER (MySQL, SQL Server):
-- SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END)

In an interview: I group by type and count the total and the number of failed ones through FILTER or SUM with CASE, and then divide. I multiply by 100.0, not by 100, because integer division in PostgreSQL discards the fractional part, and instead of 3.5 percent you'd get zero.

Task: the employee with the highest salary in each department.

You need not only the maximum value but also the name, so a simple GROUP BY is not enough. The solution is a window function or a JOIN with an aggregate.

-- a window function: RANK will show everyone if several people share the maximum
WITH r AS (
  SELECT name, department, salary,
         RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT department, name, salary FROM r WHERE rnk = 1;

-- without window functions
SELECT e.department, e.name, e.salary
FROM employees e
JOIN (SELECT department, MAX(salary) AS max_salary
      FROM employees GROUP BY department) m
  ON m.department = e.department AND m.max_salary = e.salary;

In an interview: GROUP BY with MAX gives only the amount, not the name, so I use the window function RANK partitioned by department and take rank one, so I see everyone if several people share the maximum. An alternative without window functions is to join the table with a subquery that computes the maximum per department.

Finding duplicate emails

I look for duplicates by grouping by the field and filtering groups with more than one record:

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

If duplicates may differ in case or spaces, I group by LOWER(TRIM(email)). To see the duplicate records themselves with all their fields, I use this query as a subquery in WHERE email IN (...).

The second way is the window function ROW_NUMBER() OVER (PARTITION BY email ORDER BY id), where everything with a number above 1 is a duplicate. For QA a found duplicate often means a bug: a missing UNIQUE constraint in the DB or a validation problem with parallel requests.