SQLize
Online
/
PHPize Online
/
SQLtest Online
A
A
A
Share
Donate
Blog
Popular
Donate
A
A
A
Share
Blog
Popular
SQLize.online is a free online SQL environment for quickly running, experimenting with and sharing code.
You can run your SQL code on top of the most popular RDBMS including MySQL, MariaDB, SQLite, PostgreSQL, Oracle and Microsoft SQL Server.
SQL code:
Upload
Copy
Format
Clear
------------------------------ 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
SQL
Server:
MariaDB 11.4
MariaDB 11.5
MariaDB 10
MariaDB 10 Sakila (ReadOnly)
MySQL 5.7
MySQL 5.7 Sakila (ReadOnly)
MySQL 8.0
MySQL 8.0 Sakila (ReadOnly)
SQLite 3
PostgreSQL 10 Bookings (ReadOnly)
PostgreSQL 11
PostgreSQL 12
PostgreSQL 13
PostgreSQL 14
PostgreSQL 15
MS SQL Server 2017
MS SQL Server 2019
MS SQL Server 2022
MS SQL Server 2022 AdventureWorks (ReadOnly)
Firebird 4.0
Firebird 4.0 (Employee)
Oracle Database 19c (HR)
Oracle Database 21c
Oracle Database 23c Free
SOQOL
Version
ER Diagram
Preserve result
Stuck with a problem?
Got Error?
Ask ChatGPT!
Result:
Copy
Clear