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
*/
-- 3. Calculate the average dwell time in seconds across all sessions (i.e. return one number)? Dwell time is the length of time between opening and closing the menu.
-- SELECT (SUM(end_time)-SUM(start_time))/COUNT(*) AS avg_dwell_time
-- FROM user_sessions

------------ Portal -----------

CREATE TABLE video_calls
    ([caller] int, [recepient] int, [date] datetime, [call_id] int, [duration] int)
;
    
INSERT INTO video_calls
    ([caller], [recepient], [date], [call_id], [duration])
VALUES
    (1111, 1112, '2020-01-01 00:00:00', 1, 10),
    (1111, 1003, '2020-01-01 00:00:00', 2, 15),
    (1112, 1145, '2020-01-01 00:00:00', 3, 5)
;

CREATE TABLE fb_dau
    ([user_id] int, [dau_fg] int, [date] datetime, [country] varchar(2))
;
    
INSERT INTO fb_dau
    ([user_id], [dau_fg], [date], [country])
VALUES
    (1111, 1, '2020-01-01 00:00:00', 'US'),
    (1112, 0, '2020-01-01 00:00:00', 'CA'),
    (1003, 0, '2020-01-01 00:00:00', 'US')
;
-- Q1: On 2020-01-01 how many people initiated multiple calls?

SELECT COUNT(*)
FROM(
SELECT caller
FROM video_calls
WHERE date = '2020-01-01' 
GROUP BY caller
HAVING COUNT(DISTINCT call_id)>1) AS t;

-- Q2: % of DAU used the video calls function by each country on 2020-01-01?

WITH dau AS (
    SELECT country, user_id
    FROM fb_dau
    WHERE date='2020-01-01'),
    video_user AS (
        SELECT caller AS video_user
        FROM video_calls
        GROUP BY caller
        UNION 
        SELECT recepient AS video_user
        FROM video_calls
        GROUP BY recepient)
SELECT country, CAST(COUNT(DISTINCT video_user)/COUNT(DISTINCT user_id) AS decimal(4,2)) AS percent_video_users
FROM dau AS a LEFT JOIN video_user AS b ON a.user_id = b.video_user
GROUP BY country;

-- CONVERT(date, xxx) AS date

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