CREATE TABLE table_reg (
user_id varchar(50),
registration_date date
);
INSERT INTO table_reg (user_id, registration_date) VALUES
('user001', '2024-10-01'),
('user002', '2024-10-03'),
('user003', '2024-10-05'),
('user004', '2024-10-08'),
('user005', '2024-10-10'),
('user006', '2024-10-12'),
('user007', '2024-10-15'),
('user008', '2024-10-17'),
('user009', '2024-10-20'),
('user010', '2024-10-22'),
('user011', '2024-10-25'),
('user012', '2024-10-28'),
('user013', '2024-10-30'),
('user014', '2024-11-01'),
('user015', '2024-11-03'),
('user016', '2024-11-06'),
('user017', '2024-11-08'),
('user018', '2024-11-11'),
('user019', '2024-11-14'),
('user020', '2024-11-16'),
('user021', '2024-11-18'),
('user022', '2024-11-21'),
('user023', '2024-11-24'),
('user024', '2024-11-26'),
('user025', '2024-11-29'),
('user026', '2024-12-01'),
('user027', '2024-12-03'),
('user028', '2024-12-06'),
('user029', '2024-12-09'),
('user030', '2024-12-11'),
('user031', '2024-12-13'),
('user032', '2024-12-16'),
('user033', '2024-12-18'),
('user034', '2024-12-21'),
('user035', '2024-12-23'),
('user036', '2024-12-26'),
('user037', '2024-12-28'),
('user038', '2024-12-30'),
('user039', '2024-12-31'),
('user040', '2024-10-02'),
('user041', '2024-10-07'),
('user042', '2024-10-13'),
('user043', '2024-10-19'),
('user044', '2024-11-02'),
('user045', '2024-11-05'),
('user046', '2024-11-10'),
('user047', '2024-11-17'),
('user048', '2024-11-23'),
('user049', '2024-12-02'),
('user050', '2024-12-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-10-15', 200.00),
('user003', '2024-11-01', 310.00),
('user003', '2024-11-25', 190.60),
('user003', '2024-12-10', 230.00),
('user007', '2024-11-05', 320.00),
('user007', '2024-11-15', 200.00),
('user007', '2024-12-01', 310.60),
('user007', '2024-12-20', 180.50),
('user008', '2024-11-10', 145.30),
('user008', '2024-11-20', 275.00),
('user008', '2024-12-02', 200.00),
('user008', '2024-12-15', 195.00),
('user012', '2024-12-01', 250.75),
('user012', '2024-12-10', 295.75),
('user012', '2024-12-20', 320.40),
('user012', '2025-01-01', 310.00),
('user015', '2024-12-15', 260.50),
('user015', '2024-12-20', 185.75),
('user015', '2025-01-01', 295.00),
('user015', '2025-01-10', 270.00),
('user017', '2024-12-25', 300.00),
('user017', '2025-01-01', 215.20),
('user017', '2025-01-10', 220.30),
('user019', '2025-01-05', 210.50),
('user019', '2025-01-10', 280.00),
('user019', '2025-01-15', 320.10),
('user004', '2024-10-20', 220.00),
('user004', '2024-11-01', 175.50),
('user004', '2024-11-25', 260.10),
('user005', '2024-10-25', 180.75),
('user005', '2024-11-12', 290.10),
('user005', '2024-12-05', 215.40),
('user006', '2024-10-30', 310.90),
('user006', '2024-11-10', 150.00),
('user006', '2024-11-25', 275.50),
('user009', '2024-11-15', 150.00),
('user009', '2024-11-25', 300.00),
('user009', '2024-12-10', 220.25),
('user010', '2024-11-20', 275.25),
('user010', '2024-12-01', 310.00),
('user010', '2024-12-15', 240.80),
('user011', '2024-11-25', 200.00),
('user011', '2024-12-05', 185.00),
('user011', '2024-12-20', 270.00),
('user013', '2024-12-05', 180.00),
('user013', '2024-12-15', 230.00),
('user013', '2024-12-25', 275.50),
('user014', '2024-12-10', 310.00),
('user014', '2024-12-20', 200.00),
('user014', '2025-01-01', 320.75),
('user016', '2024-12-20', 240.00),
('user016', '2024-12-25', 190.50),
('user016', '2025-01-01', 275.75),
('user018', '2024-12-30', 275.00),
('user018', '2025-01-01', 195.75),
('user018', '2025-01-10', 310.00),
('user020', '2025-01-10', 150.00),
('user020', '2025-01-15', 300.00),
('user020', '2025-01-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 5 months and who don't have any transactions
--Do this with one select.
-- Task 2 - Please select all registered users with their sum of transactions
--made after December 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.
Telegram: DB developers channel