Leetcode HARD 1336 - RECURSIVE CTE SQL Explained - Transactions Per Visit | Everyday Data Science

Question: https://leetcode.com/problems/number-...

SQL Schema:
Create table If Not Exists Visits (user_id int, visit_date date)
Create table If Not Exists Transactions (user_id int, transaction_date date, amount int)
Truncate table Visits
insert into Visits (user_id, visit_date) values ('1', '2020-01-01')
insert into Visits (user_id, visit_date) values ('2', '2020-01-02')
insert into Visits (user_id, visit_date) values ('12', '2020-01-01')
insert into Visits (user_id, visit_date) values ('19', '2020-01-03')
insert into Visits (user_id, visit_date) values ('1', '2020-01-02')
insert into Visits (user_id, visit_date) values ('2', '2020-01-03')
insert into Visits (user_id, visit_date) values ('1', '2020-01-04')
insert into Visits (user_id, visit_date) values ('7', '2020-01-11')
insert into Visits (user_id, visit_date) values ('9', '2020-01-25')
insert into Visits (user_id, visit_date) values ('8', '2020-01-28')
Truncate table Transactions
insert into Transactions (user_id, transaction_date, amount) values ('1', '2020-01-02', '120')
insert into Transactions (user_id, transaction_date, amount) values ('2', '2020-01-03', '22')
insert into Transactions (user_id, transaction_date, amount) values ('7', '2020-01-11', '232')
insert into Transactions (user_id, transaction_date, amount) values ('1', '2020-01-04', '7')
insert into Transactions (user_id, transaction_date, amount) values ('9', '2020-01-25', '33')
insert into Transactions (user_id, transaction_date, amount) values ('9', '2020-01-25', '66')
insert into Transactions (user_id, transaction_date, amount) values ('8', '2020-01-28', '1')
insert into Transactions (user_id, transaction_date, amount) values ('9', '2020-01-25', '99')

Pandas Schema:
data = [[1, '2020-01-01'], [2, '2020-01-02'], [12, '2020-01-01'], [19, '2020-01-03'], [1, '2020-01-02'], [2, '2020-01-03'], [1, '2020-01-04'], [7, '2020-01-11'], [9, '2020-01-25'], [8, '2020-01-28']]
visits = pd.DataFrame(data, columns=['user_id', 'visit_date']).astype({'user_id':'Int64', 'visit_date':'datetime64[ns]'})
data = [[1, '2020-01-02', 120], [2, '2020-01-03', 22], [7, '2020-01-11', 232], [1, '2020-01-04', 7], [9, '2020-01-25', 33], [9, '2020-01-25', 66], [8, '2020-01-28', 1], [9, '2020-01-25', 99]]
transactions = pd.DataFrame(data, columns=['user_id', 'transaction_date', 'amount']).astype({'user_id':'Int64', 'transaction_date':'datetime64[ns]', 'amount':'Int64'})

#leetcode #datascience #tutorial