Лиги Цифровой экономики — десять SQL-задач по объявлениям и маркам

Условие

Дана таблица orders(offer_id, user, create_date, finish_date, mark) — объявления.

10 заданий:

1. Для каждого пользователя — кол-во объявлений.
2. Для каждого пользователя — кол-во уникальных марок.
3. Для каждого пользователя — дата первого опубликованного объявления.
4. Дата первого снятия объявления.
5. Пользователи, у которых есть марка M1 и нет марки M4.
6. Пользователи, размещающие точно такой же набор марок, как u2.
7. T1 (4 строки) и T2 (5 строк): MIN/MAX строк после INNER, LEFT, FULL OUTER JOIN.
8. Найти дубли поля name в table.
9. Четвёртое по величине уникальное salary через оконные функции.
10. Ошибка в запросе SELECT YEAR(date) AS Year_Date FROM table WHERE Year_Date >= 2019 — где?

Бонус: подсчитать уникальных клиентов по типу a/b, причём если клиент связан с обоими — он относится к a.

Решение

1–4. Базовые агрегаты

-- 1. orders per user
SELECT "user", COUNT(*) AS n_orders FROM orders GROUP BY "user";
 
-- 2. unique marks per user
SELECT "user", COUNT(DISTINCT mark) AS n_marks FROM orders GROUP BY "user";
 
-- 3. first publish date per user
SELECT "user", MIN(create_date) AS first_create FROM orders GROUP BY "user";
 
-- 4. first finish date overall
SELECT MIN(finish_date) AS first_finish FROM orders WHERE finish_date IS NOT NULL;

5. Имеют M1, нет M4

SELECT "user"
FROM orders
GROUP BY "user"
HAVING SUM(CASE WHEN mark = 'M1' THEN 1 ELSE 0 END) > 0
   AND SUM(CASE WHEN mark = 'M4' THEN 1 ELSE 0 END) = 0;

6. Тот же набор марок, что у u2

WITH u2_marks AS (
  SELECT DISTINCT mark FROM orders WHERE "user" = 'u2'
),
user_marks AS (
  SELECT "user", mark FROM orders GROUP BY "user", mark
)
SELECT um."user"
FROM user_marks um
GROUP BY um."user"
HAVING (SELECT COUNT(*) FROM u2_marks)
       = SUM(CASE WHEN um.mark IN (SELECT mark FROM u2_marks) THEN 1 ELSE 0 END)
   AND COUNT(DISTINCT um.mark) = (SELECT COUNT(*) FROM u2_marks);

7. JOIN: MIN/MAX строк

T1 (4 rows), T2 (5 rows).

Точный MAX для full outer join при нескольких matched на одной стороне: до 20.

8. Дубли name

SELECT name, COUNT(*) AS cnt
FROM "table"
GROUP BY name
HAVING COUNT(*) > 1;

9. 4-е по величине уникальное salary

SELECT DISTINCT salary
FROM (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rk
  FROM "table"
) q
WHERE rk = 4;

10. Ошибка в WHERE Year_Date >= 2019

Year_Date — alias из SELECT. В большинстве СУБД (включая SQL Server / Postgres) алиас из SELECT недоступен в WHERE, потому что WHERE выполняется до SELECT. Корректно:

SELECT YEAR(date) AS Year_Date FROM "table"
WHERE YEAR(date) >= 2019;

Или через подзапрос/CTE.

Бонус. Унивкальные клиенты по типу с приоритетом 'a'

WITH client_type AS (
  SELECT client,
         CASE WHEN MAX(CASE WHEN type = 'A' THEN 1 ELSE 0 END) = 1
              THEN 'A' ELSE 'B' END AS effective_type
  FROM "table"
  GROUP BY client
)
SELECT effective_type, COUNT(*) AS n_clients
FROM client_type
GROUP BY effective_type;

Подводные камни

1. user — reserved word в Postgres. Заключайте в "user".
2. COUNT(DISTINCT) vs COUNT(*) в (1) и (2) — критично.
3. В (5) можно через INTERSECT и EXCEPT, но HAVING с условиями короче.
4. В (6) «точно такой же набор» — нужно проверить и subset, и superset (то есть «равенство множеств»).
5. В (10) ошибки две: alias не доступен в WHERE; и YEAR() нет в Postgres (используется EXTRACT(YEAR FROM date)).
6. В (8) для action «удалить дубли» — другой запрос (DELETE WHERE id NOT IN (...)), задача — найти.
7. В Бонусе priority 'a' переопределяется через MAX-агрегацию: если хоть одна A — клиент A.

Эталонный ответ

Стандартные SQL-агрегации, HAVING, EXISTS / NOT EXISTS, set-operations для «равенства множеств». Главная теоретическая ошибка в #10 — alias из SELECT не виден в WHERE.