SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE table_reg ( user_id varchar(50), registration_date date ); INSERT INTO table_reg (user_id, registration_date) VALUES ('user001', '2024-03-01'), ('user002', '2024-03-03'), ('user003', '2024-03-05'), ('user004', '2024-03-08'), ('user005', '2024-03-10'), ('user006', '2024-03-12'), ('user007', '2024-03-15'), ('user008', '2024-03-17'), ('user009', '2024-03-20'), ('user010', '2024-03-22'), ('user011', '2024-03-25'), ('user012', '2024-03-28'), ('user013', '2024-03-30'), ('user014', '2024-04-01'), ('user015', '2024-04-03'), ('user016', '2024-04-06'), ('user017', '2024-04-08'), ('user018', '2024-04-11'), ('user019', '2024-04-14'), ('user020', '2024-04-16'), ('user021', '2024-04-18'), ('user022', '2024-04-21'), ('user023', '2024-04-24'), ('user024', '2024-04-26'), ('user025', '2024-04-29'), ('user026', '2024-05-01'), ('user027', '2024-05-03'), ('user028', '2024-05-06'), ('user029', '2024-05-09'), ('user030', '2024-05-11'), ('user031', '2024-05-13'), ('user032', '2024-05-16'), ('user033', '2024-05-18'), ('user034', '2024-05-21'), ('user035', '2024-05-23'), ('user036', '2024-05-26'), ('user037', '2024-05-28'), ('user038', '2024-05-30'), ('user039', '2024-05-31'), ('user040', '2024-03-02'), ('user041', '2024-03-07'), ('user042', '2024-03-13'), ('user043', '2024-03-19'), ('user044', '2024-04-02'), ('user045', '2024-04-05'), ('user046', '2024-04-10'), ('user047', '2024-04-17'), ('user048', '2024-04-23'), ('user049', '2024-05-02'), ('user050', '2024-05-07'); CREATE TABLE table_act ( user_id varchar(50), date_ date, transaction_amount float ); INSERT INTO table_act (user_id, date_, transaction_amount) VALUES ('user003', '2024-03-15', 200.00), ('user003', '2024-04-01', 310.00), ('user003', '2024-04-25', 190.60), ('user003', '2024-05-10', 230.00), ('user007', '2024-04-05', 320.00), ('user007', '2024-04-15', 200.00), ('user007', '2024-05-01', 310.60), ('user007', '2024-05-20', 180.50), ('user008', '2024-04-10', 145.30), ('user008', '2024-04-20', 275.00), ('user008', '2024-05-02', 200.00), ('user008', '2024-05-15', 195.00), ('user012', '2024-05-01', 250.75), ('user012', '2024-05-10', 295.75), ('user012', '2024-05-20', 320.40), ('user012', '2024-06-01', 310.00), ('user015', '2024-05-15', 260.50), ('user015', '2024-05-20', 185.75), ('user015', '2024-06-01', 295.00), ('user015', '2024-06-10', 270.00), ('user017', '2024-05-25', 300.00), ('user017', '2024-06-01', 215.20), ('user017', '2024-06-10', 220.30), ('user019', '2024-06-05', 210.50), ('user019', '2024-06-10', 280.00), ('user019', '2024-06-15', 320.10), ('user004', '2024-03-20', 220.00), ('user004', '2024-04-01', 175.50), ('user004', '2024-04-25', 260.10), ('user005', '2024-03-25', 180.75), ('user005', '2024-04-12', 290.10), ('user005', '2024-05-05', 215.40), ('user006', '2024-03-30', 310.90), ('user006', '2024-04-10', 150.00), ('user006', '2024-04-25', 275.50), ('user009', '2024-04-15', 150.00), ('user009', '2024-04-25', 300.00), ('user009', '2024-05-10', 220.25), ('user010', '2024-04-20', 275.25), ('user010', '2024-05-01', 310.00), ('user010', '2024-05-15', 240.80), ('user011', '2024-04-25', 200.00), ('user011', '2024-05-05', 185.00), ('user011', '2024-05-20', 270.00), ('user013', '2024-05-05', 180.00), ('user013', '2024-05-15', 230.00), ('user013', '2024-05-25', 275.50), ('user014', '2024-05-10', 310.00), ('user014', '2024-05-20', 200.00), ('user014', '2024-06-01', 320.75), ('user016', '2024-05-20', 240.00), ('user016', '2024-05-25', 190.50), ('user016', '2024-06-01', 275.75), ('user018', '2024-05-30', 275.00), ('user018', '2024-06-01', 195.75), ('user018', '2024-06-10', 310.00), ('user020', '2024-06-10', 150.00), ('user020', '2024-06-15', 300.00), ('user020', '2024-06-20', 275.00); -- You have 2 tables -- table_reg - list of registered users and their dates of registration (user_id is unique) -- table_act - information about dates where users made transactions and aggregated amount of transactions for the exact date and exact user_id (transaction_amount always > 0) select * from table_reg order by user_id limit 10; select * from table_act order by user_id, date_ limit 10; -- Task 1 - Please select all registered users with registration_date in last 4 months and who don't have any transactions SELECT table_reg.user_id, table_reg.registration_date FROM table_reg LEFT JOIN table_act ON table_reg.user_id = table_act.user_id LIMIT 5; WITH users_and_transactions AS ( SELECT user_id, AVG(transaction_amount) OVER (PARTITION BY user_id) AS avg_trx ) FROM table_act ) SELECT user_id, avg_trx FROM users_and_transactions; -- Task 2 - Please select all registered users with their sum of transactions made after April 2024 (show 0 for users with no transactions). Do this with one select. -- Task 3 - Please select from the table_act all user_ids and the last date of activity for each user_id along with the transaction sum for this date (so in result it will be a unique row for each user_id). Do this using window functions.