Koddo
начать бесплатно

SQL-детектив: возвраты в кофейне «Зерно»

Второе дело, для продолжающих. Выручка одной кофейни тает, а возвраты сходятся не все: найди чеки без покупки и того, кто их оформлял. Бесплатно и без регистрации.

  • 7 шагов
  • 6 таблиц
  • 40–70 минут
  • для продолжающих
начать расследование как играть

Улики по порядку

найдено 0 из 7

Шаг 1 из 7

  1. Где утекают деньги текущий шаг

    Сравни три точки за две недели: какая вернула покупателям самую большую долю своей выручки? Считай деньги, а не число возвратов.

    к SQL-запросу

    нужно: CASE и FILTERGROUP BY

    подсказка

    Доля — сумма возвратов, делённая на сумму продаж: SUM(amount) FILTER (WHERE kind = 'возврат') или SUM(CASE WHEN … END). Умножь на 100.0, иначе целые числа поделятся нацело.

    ещё подсказка: заготовка запроса

    Заполни пропуски ___ значениями из задания и выполни запрос.

    SELECT s.name,
       100.0 * SUM(r.amount) FILTER (WHERE r.kind = '___')
             / SUM(r.amount) FILTER (WHERE r.kind = '___') AS refund_share
FROM receipts r
JOIN shops s ON s.id = r.shop_id
GROUP BY s.name
ORDER BY refund_share ___
LIMIT 1;

  2. Возвраты без покупки закрыт

    У возврата в ref_receipt_id записан чек покупки. Сколько возвратов у вокзала ссылаются на чек, которого в базе нет?

    к SQL-запросу

    нужно: LEFT JOINIS NULL

    подсказка

    Присоедини receipts к самой себе: LEFT JOIN receipts sale ON sale.id = r.ref_receipt_id. Если такой покупки нет, все колонки sale придут пустыми.

    ещё подсказка: заготовка запроса

    Заполни пропуски ___ значениями из задания и выполни запрос.

    SELECT COUNT(*)
FROM receipts r
JOIN shops s ON s.id = r.shop_id
LEFT JOIN receipts sale ON sale.id = r.___
WHERE s.name = 'Зерно у вокзала'
  AND r.kind = 'возврат'
  AND sale.id IS ___;

  3. Чей логин закрыт

    Возврат оформляют под своим логином. Чей логин оформил у вокзала больше трёх возвратов за две недели? Назови сотрудника.

    к SQL-запросу

    нужно: HAVINGJOIN

    подсказка

    receipts.login совпадает со staff.login. Сгруппируй возвраты точки по сотруднику и оставь группы, где HAVING COUNT(*) > 3.

    ещё подсказка: заготовка запроса

    Заполни пропуски ___ значениями из задания и выполни запрос.

    SELECT st.full_name, COUNT(*) AS refunds
FROM receipts r
JOIN shops s ON s.id = r.shop_id
JOIN staff st ON st.login = r.___
WHERE s.name = 'Зерно у вокзала'
  AND r.kind = 'возврат'
GROUP BY st.full_name
HAVING COUNT(*) ___;

  4. Алиби по сменам закрыт

    Сверь возвраты под логином Кима у вокзала с его сменами. Во скольких случаях Ким в ту минуту работал на другой точке?

    к SQL-запросу

    нужно: JOIN по условиюдаты и время

    подсказка

    Присоедини shifts по сотруднику и по времени: r.created_at BETWEEN sh.starts_at AND sh.ends_at. Другая точка — sh.shop_id <> r.shop_id.

    ещё подсказка: заготовка запроса

    Заполни пропуски ___ значениями из задания и выполни запрос.

    SELECT COUNT(*)
FROM receipts r
JOIN shops s ON s.id = r.shop_id
JOIN staff k ON k.login = r.login
JOIN shifts sh ON sh.staff_id = k.id
 AND r.created_at BETWEEN sh.___ AND sh.___
WHERE r.kind = 'возврат'
  AND s.name = 'Зерно у вокзала'
  AND k.full_name = 'Виктор Ким'
  AND sh.shop_id ___ r.shop_id;

  5. Куда ушли деньги закрыт

    Возврат уходит на банковскую карту. На какую карту поддельные возвраты принесли больше всего денег? Владельца найди в программе лояльности.

    к SQL-запросу

    нужно: SUM и GROUP BYLEFT JOIN

    подсказка

    Поддельные возвраты — те, что нашлись на втором шаге. Сгруппируй их по card_last4, сложи amount и присоедини customers по номеру карты.

    ещё подсказка: заготовка запроса

    Заполни пропуски ___ значениями из задания и выполни запрос.

    SELECT c.full_name, SUM(r.amount) AS total
FROM receipts r
LEFT JOIN receipts sale ON sale.id = r.ref_receipt_id
JOIN customers c ON c.card_last4 = r.___
WHERE r.kind = 'возврат'
  AND sale.id IS NULL
GROUP BY c.full_name
ORDER BY ___ DESC
LIMIT 1;

  6. Кто был у служебной двери закрыт

    Касса для возвратов стоит у служебного входа. Кто последним проходил через служебную дверь точки перед каждым поддельным возвратом?

    к SQL-запросу

    нужно: оконные функцииWITH (CTE)

    подсказка

    Для каждого поддельного возврата возьми проходы этой точки за полчаса до него и пронумеруй их с конца: ROW_NUMBER() OVER (PARTITION BY возврат ORDER BY passed_at DESC). Нужна строка с номером 1.

    ещё подсказка: заготовка запроса

    Заполни пропуски ___ значениями из задания и выполни запрос.

    WITH fake AS (
  SELECT r.id, r.shop_id, r.amount, r.created_at
  FROM receipts r
  LEFT JOIN receipts sale ON sale.id = r.ref_receipt_id
  WHERE r.kind = 'возврат' AND sale.id IS NULL
), last_pass AS (
  SELECT f.id, d.staff_id,
         ROW_NUMBER() OVER (PARTITION BY f.___ ORDER BY d.passed_at ___) AS rn
  FROM fake f
  JOIN door_log d ON d.shop_id = f.shop_id
   AND d.passed_at BETWEEN f.created_at - interval '30 minutes' AND f.created_at
)
SELECT DISTINCT st.full_name
FROM last_pass lp
JOIN staff st ON st.id = lp.staff_id
WHERE lp.rn = ___;

  7. Сколько успели вывести закрыт

    Посчитай накопительную сумму поддельных возвратов по времени. В какой день она перевалила за 10 000 ₽?

    к SQL-запросу

    нужно: SUM() OVERWITH (CTE)

    подсказка

    SUM(amount) OVER (ORDER BY created_at) даёт сумму с начала месяца на каждой строке. Сложи это в CTE и возьми первую строку, где сумма больше 10 000. Дату достанет created_at::date.

    ещё подсказка: заготовка запроса

    Заполни пропуски ___ значениями из задания и выполни запрос.

    WITH fake AS (
  SELECT r.id, r.shop_id, r.amount, r.created_at
  FROM receipts r
  LEFT JOIN receipts sale ON sale.id = r.ref_receipt_id
  WHERE r.kind = 'возврат' AND sale.id IS NULL
), running AS (
  SELECT created_at, SUM(amount) OVER (ORDER BY ___) AS total
  FROM fake
)
SELECT created_at::date AS day, total
FROM running
WHERE total > ___
ORDER BY created_at
LIMIT ___;

Прогресс и запрос сохраняются в этом браузере.

SQL-консоль

PostgreSQL в браузере только чтение
осмотреться:
Ctrl / ⌘ + Enter

Пиши SELECT: результат появится ниже. База восстанавливается перед каждым запуском.

Выполни первый запрос, чтобы осмотреть материалы дела. При первом запуске загрузится PostgreSQL — около 17 МБ.

к заданию ↑
таблицы и связи 6
Схема связей таблиц staff.shop_id ведёт в shops; shifts.staff_id ведёт в staff; door_log.staff_id ведёт в staff; receipts.login ведёт в staff; receipts.card_last4 ведёт в customers; receipts.ref_receipt_id ведёт в receipts shops shifts staff receipts door_log customers shop_idstaff_idstaff_idlogincard_last4ref_receipt_id
shop_id везде ведёт в shops.id, staff_id — в staff.id. Кто пробил чек: receipts.login = staff.login. Возврат ссылается на покупку: ref_receipt_id → receipts.id. Карта покупателя: receipts.card_last4 = customers.card_last4.

Нажми на имя таблицы, чтобы подставить запрос.

  • 3 строки три кофейни сети id, name, address
  • 13 строк сотрудники, их логины в кассе и экстренные контакты id, full_name, role, shop_id, login, phone, emergency_phone
  • 106 строк смены по графику id, staff_id, shop_id, starts_at, ends_at
  • 625 строк чеки кассы: продажи и возвраты; у возврата ref_receipt_id — чек покупки id, shop_id, kind, amount, card_last4, login, ref_receipt_id, created_at
  • 552 строки служебные двери, пропуск по карте сотрудника id, shop_id, staff_id, direction, passed_at
  • 38 строк программа лояльности: к ней привязана банковская карта id, full_name, phone, card_last4

Как играть

  1. Читай шаг и пиши запрос в консоли. Результат появляется под редактором.
  2. Впиши найденное в поле ответа. Регистр, «ё» и кавычки не важны.
  3. Верный ответ открывает следующий шаг и один из рабочих запросов, чтобы сравнить со своим.

Прогресс и последний запрос хранятся в этом браузере. Кнопка «начать заново» — над шагами расследования.

Что тренируешь

Каждая конструкция разобрана в справочнике: ссылка ведёт на статью с примерами на живой базе.

Дальше

Преподавателям

Пара на 90 минут

  1. 10 минут Досье и схема связей: разберите, как возврат ссылается на покупку через ref_receipt_id.
  2. 30 минут Шаги 1–3: условная агрегация, LEFT JOIN с IS NULL, HAVING.
  3. 35 минут Шаги 4–7: JOIN по интервалу времени, SUM по группам, ROW_NUMBER и накопительная сумма в CTE.
  4. 15 минут Разбор эталонных запросов и бонусный вопрос из развязки.
  • Регистрация не нужна: прогресс хранится в браузере ученика. На общих компьютерах в конце занятия нажми «начать заново».
  • Ответы проверяются сразу и не лежат на странице открытым текстом, проверять вручную ничего не нужно.
  • Группе, которая только начинает, подойдёт дело № 01.

Ссылка на эту страницу в материалах курса приветствуется. Диалект PostgreSQL, данные вымышлены, совпадения случайны.

Что тренирует каждый шаг

ШагКонструкции
1. Где утекают деньгиCASE и FILTER, GROUP BY
2. Возвраты без покупкиLEFT JOIN, IS NULL
3. Чей логинHAVING, JOIN
4. Алиби по сменамJOIN по условию, даты и время
5. Куда ушли деньгиSUM и GROUP BY, LEFT JOIN
6. Кто был у служебной двериоконные функции, WITH (CTE)
7. Сколько успели вывестиSUM() OVER, WITH (CTE)