SQL

Задачі зі співбесід

напиши запит

Задача: знайди другу найбільшу зарплату.

Перше уточнення, яке варто озвучити: «якщо дві людини мають максимальну зарплату — друга найбільша це та сама сума чи наступна?» Зазвичай мають на увазі наступну унікальну суму.

-- варіант 1: підзапит, повертає NULL, якщо другої немає
SELECT MAX(salary) AS second_max
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- варіант 2: DISTINCT + OFFSET
SELECT DISTINCT salary FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

-- варіант 3: DENSE_RANK, легко узагальнити до N-ї
WITH r AS (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
  FROM employees
)
SELECT DISTINCT salary FROM r WHERE dr = 2;

На співбесіді: Спершу я уточнюю, чи враховувати повтори максимальної зарплати. Найпростіше рішення — максимальна зарплата серед тих, що менші за загальний максимум. Для N-ї найбільшої зручніше DENSE_RANK. А варіант з LIMIT і OFFSET без DISTINCT дасть неправильну відповідь, якщо максимальна зарплата повторюється.

Задача: знайди email-и, що повторюються.

Групуємо за email і залишаємо групи, де більше одного рядка. Варто врахувати регістр і пробіли — Anna@test.com і anna@test.com теж можуть бути дублікатом.

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

-- з урахуванням регістру й пробілів
SELECT LOWER(TRIM(email)) AS email_norm, COUNT(*) AS cnt
FROM users
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1;

На співбесіді: Групую за email і залишаю групи, де кількість більша за одну, через HAVING. А як QA я ще нормалізую значення через LOWER і TRIM, бо дублікати з різним регістром чи пробілом — типовий баг, коли перевірка унікальності на бекенді порівнює рядки буквально.

Задача: знайди користувачів, у яких немає жодного рахунку.

Anti-join або NOT EXISTS. Варто згадати, чому не 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;

На співбесіді: Роблю LEFT JOIN користувачів з рахунками і беру тих, у кого ключ рахунку NULL. Альтернатива — NOT EXISTS. А NOT IN я тут не використовую: якщо в підзапиті трапиться NULL, він поверне нуль рядків.

Задача: топ-3 користувачі за сумою завершених депозитів.

Три таблиці, фільтр, групування, сортування й обмеження. Варто сказати, що робити з рівними сумами на третьому місці.

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;

-- якщо при рівних сумах треба показати всіх: RANK() і rnk <= 3

На співбесіді: З'єдную користувачів, рахунки й транзакції, фільтрую завершені депозити у WHERE, групую за користувачем, сортую за сумою і беру три. Уточнюю, що робити з рівними сумами на межі: LIMIT відріже довільно, а якщо треба показати всіх рівних, використовую RANK.

Задача: кількість і сума транзакцій за місяцями.

Групування за обрізаною до місяця датою. DATE_TRUNC — PostgreSQL; у MySQL використовують 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;

На співбесіді: Я обрізаю дату до місяця через DATE_TRUNC, групую за нею і рахую кількість та суму. Варто пам'ятати, що місяці без транзакцій у результат не потраплять — якщо вони потрібні з нулями, треба згенерувати перелік місяців через generate_series і приєднати LEFT JOIN.

Задача: співробітники, які заробляють більше за свого менеджера.

Класичний 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;

На співбесіді: Це self join: з'єдную таблицю співробітників саму з собою, де менеджер — інший рядок за manager_id, і порівнюю зарплати. Тут підходить INNER JOIN, бо співробітники без менеджера все одно не можуть заробляти більше за нього.

Задача: видали дублікати, залишивши по одному запису.

Пронумерувати рядки в кожній групі дублікатів і видалити всі, крім першого. Перед DELETE — обов'язково SELECT тих самих рядків і транзакція.

BEGIN;

-- що буде видалено: усі, крім найстаршого запису кожного 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;

-- видалення
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;   -- або ROLLBACK

На співбесіді: Я нумерую рядки в кожній групі дублікатів через ROW_NUMBER з PARTITION BY і видаляю все з номером більше одного, залишаючи найстаріший запис. Спочатку виконую SELECT тих самих рядків, усе роблю в транзакції. І уточнюю, чи є в дублікатів дочірні записи — інакше зовнішні ключі не дадуть видалити або каскадом зникнуть пов'язані дані.

Задача: відсоток невдалих транзакцій за кожним типом.

Умовна агрегація і обережність з цілочисельним діленням.

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;

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

На співбесіді: Групую за типом і рахую загальну кількість та кількість невдалих через FILTER або SUM з CASE, а потім ділю. Множу на 100.0, а не на 100, бо ділення цілих чисел у PostgreSQL відкидає дробову частину, і замість 3,5 відсотка вийде нуль.

Задача: співробітник з найвищою зарплатою в кожному відділі.

Потрібне не лише значення максимуму, а й ім'я — тому простого GROUP BY недостатньо. Рішення — віконна функція або JOIN з агрегатом.

-- віконна функція: RANK покаже всіх, якщо максимум ділять кілька людей
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;

-- без віконних функцій
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;

На співбесіді: GROUP BY з MAX дає лише суму, а не ім'я, тому я використовую віконну функцію RANK з поділом за відділом і беру ранг один — так побачу всіх, якщо максимум ділять кілька людей. Альтернатива без віконних функцій — з'єднати таблицю з підзапитом, що рахує максимум по відділу.

Пошук дублікатів email

Дублікати шукаю через групування за полем і фільтрацію груп, де більше одного запису:

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

Якщо дублікати можуть відрізнятися регістром чи пробілами, групую за LOWER(TRIM(email)). Щоб побачити самі записи-дублікати з усіма полями, використовую цей запит як підзапит у WHERE email IN (...).

Другий спосіб — віконна функція ROW_NUMBER() OVER (PARTITION BY email ORDER BY id), де все з номером більше 1 — дублікати. Для QA знайдений дублікат часто означає баг: відсутнє обмеження UNIQUE у БД або проблема з валідацією при паралельних запитах.