SQLize Online / PHPize Online  /  SQLtest Online

------------------------------ WITH first_payments AS (SELECT user_id, Date_trunc('day', Min(transaction_datetime)) AS first_payment_date FROM skyeng_db.payments WHERE status_name = 'success' GROUP BY 1 ORDER BY 1), ------------------------------------------------------------------------ all_dates AS (SELECT DISTINCT class_start_datetime::DATE AS dt FROM skyeng_db.classes WHERE class_start_datetime between '2016.01.01' and '2017.01.01'), ------------------------------------------------------------------------ payments_by_dates AS (SELECT user_id, transaction_datetime::DATE AS payment_date, sum(classes) AS transaction_balance_change FROM skyeng_db.payments WHERE status_name = 'success' GROUP BY user_id,payment_date ORDER BY user_id,payment_date), ------------------------------------------------------------------------ all_dates_by_user AS (SELECT user_id, dt as dt FROM first_payments a JOIN all_dates b ON b.dt>= a.first_payment_date ORDER BY user_id asc, dt asc), ----------------------------------------------------------------------- -- ------------------------------------------------------------------------ classes_by_dates as (SELECT user_id, class_start_datetime::date as class_date, count(user_id)*(-1) as classes FROM skyeng_db.classes WHERE class_status = 'success' or class_status 'failed_by_student' GROUP BY user_id, class_date ORDER BY user_id asc), -- ------------------------------------------------------------------------ payments_by_dates_cumsum AS (SELECT a.user_id, dt, Coalesce(transaction_balance_change,0) as transaction_balance_change, sum(Coalesce(transaction_balance_change,0)) over (partition by a.user_id ORDER BY dt) AS transaction_balance_change_cs FROM all_dates_by_user a left join payments_by_dates b ON a.user_id = b.user_id AND a.dt = b.payment_date), ------------------------------------------------------------------------ classes_by_dates_dates_cumsum as (SELECT a.user_id, dt, classes, sum(Coalesce(classes,0)) over (partition by a.user_id ORDER BY dt) as classes_cs FROM all_dates_by_user a FULL JOIN classes_by_dates b ON (a.user_id = b.user_id and a.dt = b.class_date)), balance AS (SELECT a.user_id, a.dt, transaction_balance_change, transaction_balance_change_cs classes, classes_cs, transaction_balance_change_cs + classes_cs as balance FROM payments_by_dates_cumsum a JOIN classes_by_dates_dates_cumsum b ON (a.user_id=b.user_id and a.dt = b.dt) ORDER BY user_id, dt LIMIT 1000) -- SELECT * -- from balance SELECT dt, sum(transaction_balance_change) as sum_tr_bal_ch, sum(transaction_balance_change_cs) as sum_tr_bal_ch_cs, sum(classes) as sum_class, sum(classes_cs) as sum_class_cs sum(balance) as sum_bal FROM balance GROUP BY dt ORDER BY dt