SQL Studio / Разборы SQL / Даты возвратов
В октябрьском отчёте получилось −50 ₽, хотя октябрьских продаж на 80 ₽. Это не обязательно ошибка: в октябре могли вернуть товары, купленные ещё в сентябре. Для отчёта о денежных событиях вычитайте возврат в месяце refund_at. Для анализа качества продаж сентябрьской когорты отнесите поздний возврат к месяцу исходной продажи. Оба метода полезны, но отвечают на разные вопросы и дают разные помесячные ряды. Важно подписать период и указать, до какой даты известны возвраты.
Пять возвратов на три заказа
Ниже синтетические заказы и возвраты в целых копейках. У заказа 11 продажа была 29 сентября на 10000 коп., а два возврата на 3000 и 2000 выполнены 4 и 11 октября. У заказа 12 продажа на 5000 и возврат на 1000 произошли в сентябре. У октябрьского заказа 13 стоимость 8000; один возврат на 1000 имеет статус pending, другой на 8000 — completed. Для расчётов учитываем только completed; даты зафиксированы в единой условной зоне времени, в формате YYYY-MM-DD HH:MM:SS.
| order_id | sold_at | gross_kopecks |
|---|---|---|
| 11 | 2026-09-29 10:00:00 | 10000 |
| 12 | 2026-09-30 18:00:00 | 5000 |
| 13 | 2026-10-02 09:00:00 | 8000 |
| refund_id | order_id | refund_at | kopecks | status |
|---|---|---|---|---|
| 1 | 11 | 2026-10-04 | 3000 | completed |
| 2 | 12 | 2026-09-30 | 1000 | completed |
| 3 | 11 | 2026-10-11 | 2000 | completed |
| 4 | 13 | 2026-10-15 | 1000 | pending |
| 5 | 13 | 2026-10-16 | 8000 | completed |
Каждый блок WITH ... VALUES ниже самодостаточен: запускайте его в свободной SQL-песочнице целиком, без загрузки данных и без изменения заданий.
1. Неправильная подпись: месяц возврата берём из даты продажи
WITH sales(order_id,sold_at,gross_kopecks) AS (
VALUES (11,'2026-09-29 10:00:00',10000),
(12,'2026-09-30 18:00:00',5000),
(13,'2026-10-02 09:00:00',8000)
), refunds(refund_id,order_id,refund_at,amount_kopecks,status) AS (
VALUES (1,11,'2026-10-04 11:00:00',3000,'completed'),
(2,12,'2026-09-30 20:00:00',1000,'completed'),
(3,11,'2026-10-11 08:00:00',2000,'completed'),
(4,13,'2026-10-15 09:00:00',1000,'pending'),
(5,13,'2026-10-16 10:00:00',8000,'completed')
)
SELECT substr(s.sold_at,1,7) AS mislabeled_refund_month,
SUM(r.amount_kopecks) AS refunded_kopecks
FROM refunds r JOIN sales s ON s.order_id=r.order_id
WHERE r.status='completed'
GROUP BY substr(s.sold_at,1,7)
ORDER BY mislabeled_refund_month;
| mislabeled_refund_month | refunded_kopecks |
|---|---|
| 2026-09 | 6000 |
| 2026-10 | 8000 |
Запрос исполняется, но колонка, названная «месяцем возврата», на самом деле вычисляется из sold_at. Поэтому в сентябре показано 6000 копеек, включая 5000, которые физически вернули в октябре; в октябре — 8000, хотя в октябре совершили возвратов на 13000. Эта агрегация может быть правильной для когортного отчёта. Ошибка — выдавать её за движение возвратов по фактическим датам.
2. Возврат по дате события: динамика месяца
Для событийного отчёта используем sold_at для продаж, refund_at для возвратов и UNION ALL, чтобы не потерять независимые операции. Не соединяем строки продажи с несколькими возвратами напрямую: такое соединение размножило бы стоимость заказа.
WITH sales(order_id,sold_at,gross_kopecks) AS (
VALUES (11,'2026-09-29 10:00:00',10000),
(12,'2026-09-30 18:00:00',5000),
(13,'2026-10-02 09:00:00',8000)
), refunds(refund_id,order_id,refund_at,amount_kopecks,status) AS (
VALUES (1,11,'2026-10-04 11:00:00',3000,'completed'),
(2,12,'2026-09-30 20:00:00',1000,'completed'),
(3,11,'2026-10-11 08:00:00',2000,'completed'),
(4,13,'2026-10-15 09:00:00',1000,'pending'),
(5,13,'2026-10-16 10:00:00',8000,'completed')
), movements AS (
SELECT substr(sold_at,1,7) AS ym, gross_kopecks AS sale_kop, 0 AS refund_kop
FROM sales
UNION ALL
SELECT substr(refund_at,1,7),0,amount_kopecks
FROM refunds WHERE status='completed'
)
SELECT ym,SUM(sale_kop) AS sold_kopecks,
SUM(refund_kop) AS refunded_kopecks,
SUM(sale_kop)-SUM(refund_kop) AS net_kopecks
FROM movements GROUP BY ym ORDER BY ym;
| ym | sold_kopecks | refunded_kopecks | net_kopecks |
|---|---|---|---|
| 2026-09 | 15000 | 1000 | 14000 |
| 2026-10 | 8000 | 13000 | -5000 |
Сентябрь: 15000 − 1000 = 14000 копеек; октябрь: 8000 − 13000 = −5000. Отрицательный октябрьский показатель — результат выбранной учебной формулы продажи минус исполненные возвраты в календарном месяце. Это не автоматическое утверждение о бухгалтерской выручке или кассовом потоке: даты признания продажи и поступления денег в настоящей системе могут отличаться.
3. Возврат на дату исходной продажи: как изменился её итог
Для оценки продаж конкретного месяца сначала сводим все завершённые возвраты до одной строки на заказ, только затем соединяем с продажей. Иначе повторные возвраты удвоят gross_kopecks.
WITH sales(order_id,sold_at,gross_kopecks) AS (
VALUES (11,'2026-09-29 10:00:00',10000),
(12,'2026-09-30 18:00:00',5000),
(13,'2026-10-02 09:00:00',8000)
), refunds(refund_id,order_id,refund_at,amount_kopecks,status) AS (
VALUES (1,11,'2026-10-04 11:00:00',3000,'completed'),
(2,12,'2026-09-30 20:00:00',1000,'completed'),
(3,11,'2026-10-11 08:00:00',2000,'completed'),
(4,13,'2026-10-15 09:00:00',1000,'pending'),
(5,13,'2026-10-16 10:00:00',8000,'completed')
), refund_per_order AS (
SELECT order_id,SUM(amount_kopecks) AS refunded_kopecks
FROM refunds WHERE status='completed' GROUP BY order_id
)
SELECT substr(s.sold_at,1,7) AS sale_ym,
SUM(s.gross_kopecks) AS sold_kopecks,
SUM(COALESCE(r.refunded_kopecks,0)) AS refunded_kopecks,
SUM(s.gross_kopecks)-SUM(COALESCE(r.refunded_kopecks,0)) AS adjusted_kopecks
FROM sales s LEFT JOIN refund_per_order r ON r.order_id=s.order_id
GROUP BY substr(s.sold_at,1,7) ORDER BY sale_ym;
| sale_ym | sold_kopecks | refunded_kopecks | adjusted_kopecks |
|---|---|---|---|
| 2026-09 | 15000 | 6000 | 9000 |
| 2026-10 | 8000 | 8000 | 0 |
Сентябрьские продажи скорректировались до 15000 − 6000 = 9000 копеек; октябрьские — до нуля. За сентябрь и октябрь в сумме оба метода дают одни и те же 9000 копеек, потому что в нашем ограниченном наборе учтены все завершённые возвраты. При незакрытом окне наблюдения когорты поздних месяцев могут выглядеть «лучше» только из-за того, что возвраты ещё не пришли; для сравнения когорт фиксируйте дату среза.
4. Границы: 1 октября 00:00 включён, 1 ноября исключён
WITH refunds(refund_id,refund_at,status,amount_kopecks) AS (
VALUES (1,'2026-09-30 23:59:59','completed',900),
(2,'2026-10-01 00:00:00','completed',125),
(3,'2026-10-31 23:59:59','completed',275),
(4,'2026-11-01 00:00:00','completed',700),
(5,'2026-10-20 12:00:00','pending',800)
)
SELECT COUNT(*) AS refunds_n,COALESCE(SUM(amount_kopecks),0) AS october_kopecks
FROM refunds
WHERE status='completed' AND refund_at>='2026-10-01'
AND refund_at<'2026-11-01';
| refunds_n | october_kopecks |
|---|---|
| 2 | 400 |
В отдельном наборе сентябрьская операция на 900 копеек и ноябрьская на 700 в октябрь не попадают. Операции на 2026-10-01 00:00:00 (125) и 2026-10-31 23:59:59 (275) включены. pending на 800 исключён. Для временных строк, нормализованных в одной зоне, удобен интервал >= начало и < начало следующего месяца. При исходных UTC-временах и отчёте по местному времени сначала согласуйте часовой пояс; простой substr(...,1,7) его не переводит.
Попробуйте сформулировать проверку
Что случится с двумя помесячными рядами, если новый completed-возврат на 200 копеек придёт 5 ноября по заказу 11? Запишите прогноз до выполнения SQL: какой месяц изменится в каждом из подходов? Для практики периодов откройте группировку доставок по месяцам или общий расчёт оплат минус возвраты — это соседние техники, а не решения этого примера.
Справка: SQLite: строки даты и strftime, SQLite: UNION ALL и JOIN. Запросы даны для SQLite, не для MySQL/PostgreSQL.
В бесплатном SQL Studio используем SQLite через браузер. Отдельный курс SQL для аналитика на Stepik использует MySQL 8: его синтаксис и календарные функции не во всём совпадают с SQLite.