Получить запрос , который собирает данные о балансах студентов за каждый прожитый ими день в 2016 году
/*
Шаг 1:
Узнаём дату первой успешной транзакции для каждого студента: с этой даты собираем баланс его уроков
*/
with first_payments as
(select user_id, min(transaction_datetime::date) as first_payment_date
from skyeng_db.payments
where status_name ='success'
group by user_id),
/*
Шаг 2:
Собирем таблицу с датами за каждый календарный день 2016 года
*/
all_dates as
(select distinct date_trunc( 'day', class_start_datetime::date) as dt
from skyeng_db.classes
where class_start_datetime between '2016-01-01'::date and '2017-01-01'::date
order by dt asc),
/*
Шаг 3:
Узнаем за какие даты имеет смысл собирать баланс для студента (после того как произошла первая транзакция)
*/
all_dates_by_user as
(select user_id, dt
from first_payments
join all_dates on all_dates.dt >= first_payments.first_payment_date
order by 1),
/*
Шаг 4:
1. Ищем изменения балансов: выбираем все транзакции из payments, группируем по user_id и дате транзакции, а затем ищему сумму по полю classes
2. Получаем CTE payments_by_dates с полями: user_id, payment_date, transaction_balance_change (сколько уроков было начислено или списано в этот день)
*/
payments_by_dates as
(select user_id, date_trunc('day', transaction_datetime) as payment_date, sum(classes) as transaction_balance_change
from skyeng_db.payments
where status_name = 'success'
group by user_id, payment_date),
/*
Шаг 5:
1. Ищем баланс студентов, который сформирован только транзакциями: объединяем all_dates_by_user и payments_by_dates так, чтобы совпадали даты и user_id
2. Ищем кумулятивную сумму по полю transaction_balance_change для всех строк до текущей включительно с разбивкой по user_id и сортировкой по dt
3. Получаем CTE payments_by_dates_cumsum с полями: user_id, dt, transaction_balance_change — transaction_balance_change_cs
*/
payments_by_dates_cumsum as
(select a.user_id, a.dt, transaction_balance_change, sum (p.transaction_balance_change) over (partition by a.user_id order by a.dt) as transaction_balance_change_cs
from all_dates_by_user as a
left join payments_by_dates as p
on a.dt = p.payment_date and p.user_id = a.user_id
order by 1),
/*
Шаг 6:
Ищем изменения балансов, которое происходит в результате прохождения уроков
1. Создааем CTE classes_by_dates, посчитав в таблице classes количество уроков за каждый день для каждого ученика
2. Исключаем уроки со статусами: success и failed_by_student
3. Получаем результат с такими полями: user_id, class_date, classes (количество пройденных в этот день уроков)
— Значения classes умножаем на -1, чтобы отразить, то что «-» это списания с баланса
*/
classes_by_dates as
(select user_id, date_trunc('day', class_start_datetime) as class_date,
(count (id_class) over (partition by user_id, date_trunc('day', class_start_datetime) order by user_id)) * -1 as classes
from skyeng_db.classes
where class_status ~ 'success|failed_by_student'
order by 1,2),
/*
Шаг 7:
Сохраним кумулятивную суммы количества пройденных уроков
1. Объединяем таблицы all_dates_by_user и classes_by_dates так, чтобы совпадали даты и user_id
2. Используем оконные выражения, чтобы найти кумулятивную сумму по полю classes для всех строк до текущей включительно с разбивкой по user_id и сортировкой по dt
3. Получаем CTE classes_by_dates_dates_cumsumс полями: user_id, dt, classes — classes_cs(кумулятивная сумма по classes)
— При подсчете кумулятивной суммы обязательно нужно заменить пустые значения нулями
*/
classes_by_dates_dates_cumsum as
(select a.user_id, a.dt, classes,sum(coalesce(classes, 0)) over (partition by a.user_id order by a.dt) as classes_cs
from all_dates_by_user as a
left join classes_by_dates as c on a.user_id = c.user_id and a.dt = c.class_date
),
/*
Шаг 8:
Создадим CTE balances с вычисленными балансами каждого студента
1. Объединим таблицы payments_by_dates_cumsum и classes_by_dates_dates_cumsum так, чтобы совпадали даты и user_id
2. Получим такие поля: user_id, dt, transaction_balance_change, transaction_balance_change_cs, classes, classes_cs, balance (classes_cs + transaction_balance_change_cs).
*/
balances as
(select
p.user_id,
p.dt,
p.transaction_balance_change,
p.transaction_balance_change_cs,
c.classes,
c.classes_cs,
c.classes_cs + p.transaction_balance_change_cs as balance
from payments_by_dates_cumsum p
join classes_by_dates_dates_cumsum c
on p.user_id=c.user_id
and p.dt=c.dt)
-- Шаг 9
select
dt,
sum(transaction_balance_change) as sum_transaction_balance_change,
sum(transaction_balance_change_cs) as sum_transaction_balance_change_cs,
case when sum(classes) is null then 0 else sum(classes) * -1 end as sum_classes,
sum(classes_cs) as sum_classes_cs,
sum(balance) as sum_balance
from balances
group by dt
order by dt
https://puu.sh/IhKdk/09e420d6a7.png
Случаи отрицательных балансов есть, но их быть не должно. Вероятно, что у некоторых студентов были куплены уроки в прошлом году ещё до того года, который смотрим. Также, возможно, каким-то образом ученики покупали уроки в долг.
Присутствует цикличность — на выходных студенты проходят меньше уроков, чем в будние дни. Самые высокие сезоны по оплатам перед началом учебного года (осень) и перед началом нового года (первая половины зимы).