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;
select * from table_act;
-- Task 1 - Please select all registered users with registration_date in last 4 months and who don't have any transactions
select *
from table_reg
WHERE user_id not in (select distinct user_id from table_act);
-- 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.
SELECT tr.user_id, COALESCE(SUMIF(ta.date_ >='2024-04-01',ta.transaction_amount),0) as trans_sum
FROM table_reg tr
LEFT JOIN table_act ta
ON tr.user_id = ta.user_id
GROUP BY 1;
-- 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.
Telegram: DB developers channel