Классические SQL задачи собесов

Зачем это знать

Некоторые SQL задачи появляются на каждом собесе. Знать шаблон решения — экономит 5 минут в стрессе.

1. Second highest salary

-- Способ 1: DENSE_RANK (handles ties)
SELECT salary FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) t WHERE rnk = 2;

-- Способ 2: LIMIT OFFSET
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

-- Способ 3: Subquery
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

2. Nth highest

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

3. Top 3 salaries per department

WITH ranked AS (
    SELECT department, name, salary,
           DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
    FROM employees
)
SELECT department, name, salary
FROM ranked
WHERE rnk <= 3;

4. Duplicate emails

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

5. Users with no orders

-- LEFT JOIN
SELECT u.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

-- NOT EXISTS
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders WHERE user_id = u.id);

6. Running total

SELECT day, revenue,
       SUM(revenue) OVER (ORDER BY day) AS cumulative
FROM daily;

7. Month-over-month change

SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev,
       revenue - LAG(revenue) OVER (ORDER BY month) AS diff,
       (revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0 /
           NULLIF(LAG(revenue) OVER (ORDER BY month), 0) AS pct_change
FROM monthly;

8. First order of each user

-- Approach 1: ROW_NUMBER
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS rn
    FROM orders
) t WHERE rn = 1;

-- Approach 2: MIN
SELECT o.* FROM orders o
WHERE o.created_at = (
    SELECT MIN(created_at) FROM orders WHERE user_id = o.user_id
);

9. Consecutive days (streaks)

WITH numbered AS (
    SELECT user_id, day,
           day - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day))::int AS grp
    FROM logins
)
SELECT user_id, MIN(day), MAX(day), COUNT(*) AS streak
FROM numbered
GROUP BY user_id, grp;

Gap and islands pattern.

10. Median

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

-- Universal (ROW_NUMBER)
WITH ordered AS (
    SELECT salary,
           ROW_NUMBER() OVER (ORDER BY salary) AS rn,
           COUNT(*) OVER () AS n
    FROM employees
)
SELECT AVG(salary) AS median
FROM ordered
WHERE rn IN ((n+1)/2, (n+2)/2);

11. Gaps (missing dates)

-- Generate expected
WITH expected AS (
    SELECT generate_series('2026-01-01'::DATE, '2026-04-23'::DATE, '1 day') AS d
)
SELECT e.d FROM expected e
LEFT JOIN orders o ON DATE(o.created_at) = e.d
WHERE o.id IS NULL;

12. Rolling 7-day sum

SELECT day, revenue,
       SUM(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7
FROM daily;

13. Retention calculation

WITH cohorts AS (
    SELECT user_id, DATE_TRUNC('month', MIN(created_at)) AS cohort
    FROM users GROUP BY user_id
),
activity AS (
    SELECT DISTINCT user_id, DATE_TRUNC('month', created_at) AS active_m
    FROM events
)
SELECT cohort,
       EXTRACT(MONTH FROM AGE(active_m, cohort)) AS months_since,
       COUNT(DISTINCT user_id) AS active
FROM cohorts JOIN activity USING(user_id)
GROUP BY 1, 2;

14. Funnel conversion

WITH user_steps AS (
    SELECT user_id,
           MAX(CASE WHEN event = 'signup' THEN 1 ELSE 0 END) AS signed,
           MAX(CASE WHEN event = 'active' THEN 1 ELSE 0 END) AS active,
           MAX(CASE WHEN event = 'paid' THEN 1 ELSE 0 END) AS paid
    FROM events GROUP BY user_id
)
SELECT
    SUM(signed) AS signups,
    SUM(active) AS activations,
    SUM(paid) AS purchases,
    SUM(active) * 100.0 / SUM(signed) AS s_to_a_pct
FROM user_steps;

15. Pivot (manual)

-- Wide format
SELECT
    month,
    SUM(CASE WHEN product = 'A' THEN revenue END) AS A,
    SUM(CASE WHEN product = 'B' THEN revenue END) AS B,
    SUM(CASE WHEN product = 'C' THEN revenue END) AS C
FROM sales GROUP BY month;

16. Self-join (hierarchy)

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

17. All pairs (products together)

SELECT p1.product, p2.product
FROM sales p1
JOIN sales p2 ON p1.order_id = p2.order_id
WHERE p1.product < p2.product;

18. Users active today and yesterday

SELECT DISTINCT user_id FROM events
WHERE DATE(created_at) = CURRENT_DATE - 1
INTERSECT
SELECT DISTINCT user_id FROM events
WHERE DATE(created_at) = CURRENT_DATE;

19. Difference между two tables

-- In A, not in B
SELECT * FROM a
EXCEPT
SELECT * FROM b;

20. Random sample

-- Postgres
SELECT * FROM users TABLESAMPLE BERNOULLI(10);

-- Portable
SELECT * FROM users ORDER BY RANDOM() LIMIT 1000;

21. Pagination

SELECT * FROM users
ORDER BY id
LIMIT 20 OFFSET 40;  -- Page 3, 20 per page

-- Faster: cursor-based
SELECT * FROM users WHERE id > last_seen_id ORDER BY id LIMIT 20;

22. Unique combinations

SELECT DISTINCT col1, col2 FROM TABLE;

23. Delete duplicates keep one

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

24. Running count distinct

Harder.

WITH first_occurrence AS (
    SELECT user_id, MIN(day) AS first_day
    FROM events GROUP BY user_id
)
SELECT day,
       (SELECT COUNT(*) FROM first_occurrence WHERE first_day <= day) AS users_ever
FROM calendar;

25. Year-over-year

SELECT
    EXTRACT(MONTH FROM DATE) AS month,
    EXTRACT(YEAR FROM DATE) AS year,
    SUM(revenue) AS rev,
    LAG(SUM(revenue), 12) OVER (ORDER BY DATE) AS prev_year
FROM daily
GROUP BY 1, 2;

Patterns summary

• Top-N per group → window function + rank
• Running totals → SUM OVER
• MoM / YoY → LAG
• Gaps → generate_series + LEFT JOIN
• Streaks → gaps and islands
• Funnels → conditional aggregation
• Retention → cohort + offset + aggregate
• Hierarchies → recursive CTE

На собесе

Use these patterns. Explain which fits.

Связанные темы

• 50 вопросов SQL
• Задачи SQL
• Шпаргалка SQL с нуля
• Задачи оконные функции

FAQ

Запомнить all 25?

Patterns — да. Syntax — reference. Build over time.

Speed от practice?

Yes. Много раз solved — reflex.

LeetCode Database?

Good practice. Medium-hard.

---

Тренируйте SQL — откройте тренажёр с 1500+ вопросами для собесов.