SELECT a.* FROM user_sessions AS a INNER JOIN dim_all_users AS b ON a.user_id = b.user_id WHERE date = '2020-09-01' AND country = 'US')
--- FB Spam friend request---
CREATE TABLE users
([user_id] int, [country] varchar(2), [facebook_age] int)
;
INSERT INTO users
([user_id], [country], [facebook_age])
VALUES
(1001, 'US', 120),
(1002, 'CA', 400),
(1003, 'MX', 6),
(5555, 'US', 8)
CREATE TABLE friend_requests
([date] datetime, [sender_id] int, [receiver_id] int, [result] int)
INSERT INTO friend_requests
([date], [sender_id], [receiver_id], [result])
('2020-01-01 00:00:00', 1001, 9991, 0),
('2020-01-01 00:00:00', 1001, 1032, 1),
('2020-01-01 00:00:00', 1002, 5555, 2),
('2020-01-01 00:00:00', 1002, 1234, 1),
('2020-01-02 00:00:00', 1003, 1002, 1)
-- Q1: For each month in 2020, what is the friend request acceptance rate?
/*
SELECT month(date), SUM(CASE WHEN result=1 THEN 1.00 ELSE 0 END)/COUNT(*) AS accept_rate
FROM friend_requests
GROUP BY month(date)
*/