CREATE TABLE Users (
ID INT,
NAME VARCHAR(50),
Country VARCHAR(50)
);
INSERT INTO Users (ID, NAME, Country) VALUES
(111, 'Joe', 'US'),
(222, 'Jen', 'US'),
(333, 'Bob', 'UK'),
(444, 'Dan', 'UK'),
(555, 'Chris', 'Canada'),
(666, 'Kelly', 'Canada');
-- Create Payments table
CREATE TABLE Payments (
Number INT,
Date DATE,
User_ID INT,
Payment_Source VARCHAR(50),
Cost INT
);
INSERT INTO Payments (Number, Date, User_ID, Payment_Source, Cost) VALUES
(1, '2024-01-01', 111, 'Store', 25),
(2, '2024-01-01', 222, 'Piggy Bank', 5),
(3, '2024-01-01', 555, 'Personal offer', 30),
(4, '2024-02-01', 111, 'Store', 100),
(5, '2024-02-01', 333, 'Piggy Bank', 75),
(6, '2024-02-01', 333, 'Personal offer', 80),
(7, '2024-02-01', 111, 'Piggy Bank', 150),
(8, '2024-02-02', 222, 'Personal offer', 300),
(9, '2024-03-01', 444, 'Store', 1000),
(10, '2024-03-01', 444, 'Personal offer', 25),
(11, '2024-03-01', 555, 'Piggy Bank', 60),
(12, '2024-03-01', 111, 'Store', 700),
(13, '2024-03-01', 222, 'Store', 200);
SELECT User_ID,
MAX(Case
WHEN Cost>=100 THEN 1
ELSE 0
END) AS 'more than 100?',
COUNT(CASE
WHEN Date BETWEEN '1/1/2024 ' AND '3/1/2024' THEN 1
END
) AS `num of pmts last 3 mnths`,
SUM(Cost) AS 'Total Revenue'
FROM Users
JOIN Payments
ON Users.ID = Payments.User_ID
GROUP BY User_ID
;
````````````````````````````````````````````````
WITH previous_query AS(SELECT User_ID,
MAX(Case
WHEN Cost>=100 THEN 1
ELSE 0
END) AS 'more than 100?',
COUNT(CASE
WHEN Date BETWEEN '1/1/2024 ' AND '3/1/2024' THEN 1
END
) AS `num of pmts last 3 mnths`,
SUM(Cost) AS 'Total Revenue'
FROM Users
JOIN Payments
ON Users.ID = Payments.User_ID
GROUP BY User_ID
)
SELECT Total Revenue FROM previous_query
LEFT JOIN Users
ON Users.ID = previous_query.User_ID;
--SELECT Country,
--Total Revenue
--FROM Users;
--LEFT JOIN previous_query
--ON previous_query.User_ID = Users.ID;
Telegram: DB developers channel