LC 1107 & LC 1113 & LC 1132 & LC 1149

SQL-Basic
SQL
Publish Date:   2022-04-12

The following basic-level SQL problems.

LeetCode 1107

New Users Daily Count (Medium) [link]

SELECT login_date, COUNT(*) AS user_count
FROM(
    SELECT user_id, MIN(activity_date) AS login_date
    FROM Traffic
    WHERE activity = 'login'
    GROUP BY user_id
) t
WHERE DATEDIFF('2019-06-30', login_date) <= 90 
GROUP BY login_date;

LeetCode 1113

Reported Posts (Easy) [link]

SELECT extra AS report_reason, COUNT(DISTINCT post_id) as report_count
FROM Actions
WHERE action_date = '2019-07-04' AND action = 'report' AND extra IS NOT NULL
GROUP BY extra;

LeetCode 1132

Reported Posts II (Medium) [link]

SELECT ROUND(AVG(averages) * 100, 2) AS average_daily_percent
FROM(
    SELECT a.action_date, COUNT(DISTINCT r.post_id) / COUNT(DISTINCT a.post_id) AS averages
    FROM Actions a
    LEFT JOIN Removals r
    ON a.post_id = r.post_id
    WHERE extra = 'spam'
    GROUP BY a.action_date
) t;

LeetCode 1149

Article Views II (Medium) [link]

SELECT DISTINCT viewer_id AS id
FROM Views
GROUP BY view_date, viewer_id
HAVING COUNT(DISTINCT article_id) > 1
ORDER BY viewer_id;
SQL-Basic
  TOC