LeetCode 1149. Article Views II SQL Solution
Problem
LeetCode SQL Problem
- Article Views II
Views table
| article_id | author_id | viewer_id | view_date |
|---|---|---|---|
| 1 | 3 | 5 | 2019-08-01 |
| 3 | 4 | 5 | 2019-08-01 |
| 1 | 3 | 6 | 2019-08-02 |
| 2 | 7 | 7 | 2019-08-01 |
| 2 | 7 | 6 | 2019-08-02 |
| 4 | 7 | 1 | 2019-07-22 |
| 3 | 4 | 4 | 2019-07-21 |
| 3 | 4 | 4 | 2019-07-21 |
Solution
- MySQL
- TSQL
Article Views II
WITH ArticleViewsByDateViewerId
AS (
-- For each date, find out how many distinct articles each viewer reads
SELECT view_date
,viewer_id
,count(DISTINCT article_id) AS article_views
FROM Views
GROUP BY view_date
,viewer_id
)
-- Return viewers who viewed more than one article on the same date
SELECT DISTINCT (viewer_id) AS id
FROM ArticleViewsByDateViewerId
WHERE article_views > 1
-- Sort viewer_id in ascending order
ORDER BY viewer_id
Article Views II
WITH ArticleViewsByDateViewerId
AS (
-- For each date, find out how many distinct articles each viewer reads
SELECT view_date
,viewer_id
,count(DISTINCT article_id) AS article_views
FROM Views
GROUP BY view_date
,viewer_id
)
-- Return viewers who viewed more than one article on the same date
SELECT DISTINCT (viewer_id) AS id
FROM ArticleViewsByDateViewerId
WHERE article_views > 1
-- Sort viewer_id in ascending order
ORDER BY viewer_id
Query Output
| id |
|---|
| 5 |
| 6 |