Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
sql_fb
--- 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
run
|
edit
|
history
|
help
0
Suikwan tests
MixT
Delete double entries from a table without primary key
DeletorWall
tt
bc160402152
hhhhhhhhhhh
Sql server 2012 features list
1
The relational division