学习帖， 每天总结今天学习成果

SQL5
Table: sms_message    columns:date, country, cell_number, carrier, type(confirmation, notification)
Table: confirmation.     columns: date, cell_number
Q1: How many confirmation texts by country yesterday?
SELECT
country, COUNT(*) AS num_texts
FROM sms_message
WHERE DATEDIFF(CURDATE(), date) = 1
GROUP BY country;

Q2: Number of users who receive notification every single day in the last 7 days.
SELECT
COUNT(cell_number) AS num_users
FROM sms_message
WHERE DATEDIFF(CURDATE(), date) <= 7 AND type = 'notification'
GROUP BY cell_number
HAVING COUNT(DISTINCT date) = 7;

Q3: On Mar 26th, the confirmation rate
SELECT
(SELECT COUNT(*) FROM confirmation WHERE date = '2019-03-26')/(SELECT COUNT(*) FROM sms_message WHERE date = '2019-03-26' AND type = 'confirmation') AS confirmation_rate;

SQL6
Table: ad4ad     columns: date, userid, event(impression, click, create_ad), unitid(id for template shown to potential advertiser), cost, spend, ad_id(Only available when create_ad)
Table: user.       columns: userid, country, age
Q1: Last 30 days, by country, the total cost of the product.
SELECT
u.country, SUM(IFNULL(cost, 0)) AS total_cost
FROM user u
LEFT JOIN ad4ad a
ON u.userid = a.userid
    AND DATEDIFF(CURDATE(), a.date) <= 30
GROUP BY u.country;

Q2: How many impressions before users create an ad given a unit?
SELECT
t1.userid, t1.unitid, COUNT(*) AS num_impressions
FROM ad4ad t1
JOIN (SELECT
DISTINCT userid, unitid
FROM ad4ad
WHERE event = 'create_ad') t2
ON t1.userid = t2.userid
    AND t1.unitid = t2.unitid
    AND t1.event = 'impression'
GROUP BY t1.userid, t1.unitid;

SQL7
Table: user_actions       columns: date, userid, postid, action ('view', 'like', 'reaction', 'report', 'comment', 'reshare'), extra('love', 'spam', 'nudity')
Q1: How many posts were reported yesterday for each reason?
SELECT
extra, COUNT(DISTINCT postid) AS num_posts
FROM user_actions
WHERE DATEDIFF(CURDATE(), date) = 1
    AND action = 'report'
GROUP BY extra;

Table: reviewer_removal      Columns: date, reviewer, postid
Q2: What percent of daily content users view is actually spam?
Assume, as long as postid appears on the reviewer_removal table, it is actually spam.
All the actions in user_action are viewed by users.
SELECT
date, IFNULL(COUNT(DISTINCT r.postid)/COUNT(DISTINCT u.postid), 0) AS rate
FROM user_actions u
LEFT JOIN reviewer_removal r
ON u.postid = r.postid
GROUP BY date;

Q3: How to find user who abuse the spam system?
Find actual spam message divided by reported spam message for each user.

SELECT
a.userid, COUNT(DISTINCT r.postid)/COUNT(DISTINCT a.postid) AS rate
FROM(
SELECT
userid, postid
FROm user_actions
WHERE action = 'report'
    AND extra = 'spam') a
LEFT JOIN reviewer_removal r
ON a.postid = r.postid
GROUP BY a.userid;

SQL8
Table: ad_info     columns: ad_account, date, spend, status(open, close, fraud)
Q1: Find the percentage of fraud account in active account. (spend > 0 means active account)
SELECT
SUM(CASE WHEN status = 'fraud' THEN 1 ELSE 0 END)/COUNT(*) AS fraud_rate
FROM ad_info
WHERE spend > 0;

Q2: How many accounts are labelled fraud today?
SELECT
COUNT(*) as num_accounts
FROM ad_info
WHERE date = CURDATE()
    AND status = 'fraud'

SQL9
Table: content     columns: content_id, content_type(comment, post), target_id(Only available if content type is 'comment')
Q1: What is the comment distribution?
SELECT
c.num_comments, COUNT(a.content_id) AS num_posts
FROM
(SELECT
a.content_id, COUNT(b.target_id) AS num_comments
FROM
(SELECT
content_id
FROM content
WHERE content_type = 'post') a
LEFT JOIN (SELECT
target_id
FROM
content
WHERE content_type = 'comment'
) b
ON a.content_id = b.target_id
GROUP BY a.content_id) c
GROUP BY c.num_comments
;

Q2: content_type becomes (comment, post, video, photo, article), what is the comment distribution for each type?
SELECT
c.content_type, c.num_comments, COUNT(c.content_id) AS num_posts
FROM
(SELECT
a.content_tyoe, a.content_id, COUNT(b.target_id) AS num_comments
FROM
(SELECT
content_type, content_id
FROM content
WHERE content_type != 'comment') a
LEFT JOIN (SELECT
target_id
FROM
content
WHERE content_type = 'comment'
) b
ON a.content_id = b.target_d
GROUP BY a.content_type, a.content_id) c
GROUP BY c.content_type, c.num_comments;

SQL10
Table: action      columns: date, userid, sessionid, action(enter, click, send, exit)
Table: session   columns: date, sessionid, timespent
Q1: Average sessions per user for each day in the last 30 days?
SELECT
date, COUNT(DISTINCT sessionid)/COUNT(DISTINCT userid) AS avg_session
FROM action
WHERE DATEDIFF(CURDATE(), date) <= 30
GROUP BY date;

Q2: Time distribution of each user
SELECT
x.total_time, COUNT(x.userid) AS num_users
(SELECT
a.userid, SUM(timespent) AS total_time
FROM
(SELECT
userid, sessionid
FROM action
WHERE action = 'exit') a
JOIN session s
ON a.sessionid = s.sessionid
    AND a.date = s.date
GROUP BY a.userid) x
GROUP BY x.total_time;

SQL11
Table: friends     columns: user1, user2
Table: actions.    columns: sender, recipient, action, date
Q1: The interaction of each pair of friends (action = 'create' means one interaction)
SELECT
f.user1, f.user2, COUNT(a.sender) AS num_interactions
FROM friends f
LEFT JOIN actions a
ON a.action = 'create'
    AND (f.user1 = a.sender AND f.user2 = a.recipient) OR (f.user1 = a.recipient AND f.user2 = a.sender)
GROUP BY f.user1, f.user2;

OR:
SELECT
a.sender, a.recipient, COUNT(*) AS num_interactions
FROM
(SELECT
sender, recipient
FROM actions
WHERE action = 'create'
UNION ALL
SELECT
recipient, sender
FROM actions
WHERE action = 'create') a
GROUP BY a.sender, a.recipient;

SQL12
Table: message.   columns: date, userid, message_sends
Table: failed_message.   columns: date, userid, failed_message_sends
Q1: average number of successful message sends for users who didn't have message_send failure on a given day
SELECT
SUM(message_sends)/COUNT(DISTINCT userid) AS avg_num
FROM message
WHERE userid NOT IN (SELECT userid FROM failed_message WHERE date = 'xxx')
    AND date = 'xxx'

Q2: average number of successful message sends for users who have failed message sent on that day
SELECT
SUM(m.message_sends - f.failed_message_sends)/COUNT(m.userid) AS avg_num
FROM message m
JOIN failed_message f
ON m.userid = f.userid
    AND m.date = 'xxx'
    AND f.date = 'xxx';

SQL13
Table: song     Columns: time, userid, songid
Table: friends    Columns: userid1, userid2
Q1: What is the most popular song today?
SELECT
songid, COUNT(userid)
FROM song
GROUP BY songid
WHERE time = CURDATE();

Q2: Find friends pair who share more than two songs.
SELECT
f.userid1, f.userid2
FROM friends f
JOIN song s1
ON s1.userid = f.userid1
JOIN song s2
ON f.userid2 = s2.userid
    AND s1.songid = s2.songid
GROUP BY f.userid1, f.userid2
HAVING COUNT(DISTINCT s1.songid) > 2;

SQL14
Table: adv_info      columns: advertiser_id, ad_id, spend
Table: ad_info       columns: ad_id, user_id, price
Q1: The fraction of advertiser who at least has one conversion.
SELECT
(SELECT COUNT(DISTINCT advertiser_id) FROM adv_info WHERE ad_id IN (SELECT DISTINCT ad_id FROM ad_info))/(SELECT COUNT(DISTINCT advertiser_id) FROM adv_info) AS rate;

SELECT
COUNT(DISTINCT a1.advertiser_id)/(SELECT COUNT(DISTINCT advertiser_id) FROM adv_info) AS rate
FROM adv_info a1
JOIN ad_info a2
ON a1.ad_id = a2.ad_id;


Q2: What metrics would you show to advertiser?
SELECT
a1.advertiser_id, a1.ad_id, (a2.total_revenue - a1.spend) AS profit
FROM adv_info a1
LEFT JOIN (
SELECT
ad_id, SUM(price) AS total_revenue
FROM
ad_info
GROUP BY ad_id
) a2
ON a1.ad_id = a2.ad_id;