sql_fb, Sql Server

Language: Layout:

--- 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])
VALUES
    ('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)
*/
----

/*
WITH month_table AS (
    SELECT '2020-01-01' AS month
    UNION
    SELECT '2020-02-01' AS month
    UNION
    SELECT '2020-03-01' AS month
    UNION
    SELECT '2020-04-01' AS month
    UNION
    SELECT '2020-05-01' AS month
    UNION
    SELECT '2020-06-01' AS month
    UNION
    SELECT '2020-07-01' AS month
    UNION
    SELECT '2020-08-01' AS month
    UNION
    SELECT '2020-09-01' AS month
    UNION
    SELECT '2020-10-01' AS month
    UNION
    SELECT '2020-11-01' AS month
    UNION
    SELECT '2020-12-01' AS month),
    monthly_request AS (
    SELECT a.month, SUM(CASE WHEN b.result=1 THEN 1.00 ELSE 0 END) AS accepts, COUNT(b.result) AS requests
    FROM month_table AS a LEFT JOIN friend_requests AS b ON month(a.month)=month(b.date)
    GROUP BY a.month)
    SELECT month, CASE WHEN requests = 0 then 0.00 ELSE accepts/requests END AS accept_rate
    FROM monthly_request;
    
-- Q2: What percent of users in the United States accepted a friend request last week?
WITH R7users AS (
    SELECT *
    FROM users
    Where facebook_age>=7 AND country='US')

SELECT CAST(COUNT(DISTINCT receiver_id)/COUNT(DISTINCT a.user_id) AS DECIMAL(5,2)) AS US_acceptedRateUser
FROM R7users AS a LEFT JOIN 
(SELECT DISTINCT receiver_id FROM friend_requests WHERE date >= '2020-01-01' AND result=1) AS b ON a.user_id = b.receiver_id

*/
--- Dwell time ---

CREATE TABLE user_sessions
    ([date] datetime, [user_id] int, [start_time] int, [end_time] int, [end_reason] varchar(9))
;
    
INSERT INTO user_sessions
    ([date], [user_id], [start_time], [end_time], [end_reason])
VALUES
    ('2020-09-01 00:00:00', 1, 1964783746, 1964783924, 'close_app'),
    ('2020-09-01 00:00:00', 1, 1964783528, 1964783809, 'crash'),
    ('2020-09-02 00:00:00', 2, 1964783123, 1964783345, 'close_app'),
    ('2020-09-02 00:00:00', 3, 1964783252, 1964783658, 'crash')
;

CREATE TABLE dim_all_users
    ([user_id] int, [country] varchar(10), [is_active] varchar(5))
;
    
INSERT INTO dim_all_users
    ([user_id], [country], [is_active])
VALUES
    (1, 'US', 'true'),
    (2, 'US', 'false'),
    (3, 'CANADA', 'true')
;

/*
WITH date_tbl AS (
    SELECT date
    FROM user_sessions
    GROUP BY date)
    ,
    user_date AS (
        SELECT user_id, country, is_active, date
        FROM dim_all_users AS a CROSS JOIN date_tbl AS b)

SELECT a.user_id, a.date, SUM(CASE WHEN b.start_time IS NULL THEN 0.00 ELSE b.end_time-start_time END)/1000 AS dwell_time
FROM user_date AS a LEFT JOIN user_sessions AS b ON a.user_id = b.user_id AND a.date = b.date
GROUP BY a.user_id, a.date;
*/
-- 2. What is the percentage of active users who crashed when using Facebook from the US
-- area on a specific date?

WITH user_sessions1 AS (
    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')
    
SELECT CAST(COUNT(DISTINCT b.user_id)/COUNT(DISTINCT a.user_id) AS decimal(4,2)) AS crash_rate_us
FROM user_sessions1 AS a LEFT JOIN
(SELECT * FROM user_sessions1 WHERE end_reason='crash') AS b 
on a.user_id = b.user_id

View schema
Execution time: 0,05 sec, rows selected: 1, rows affected: 16, absolute service time: 0,2 sec, absolute service time: 0,21 sec