1149 Article Views II

Description

Table: Views

• This table may have duplicate rows.
• Each row of this table indicates that some viewer viewed an article (written by some author) on some date.
• Note that equal author_id and viewer_id indicate the same person.

Problem Statement

Write a solution to find all the people who viewed more than one article on the same date.

Return the result table sorted by id in ascending order.

The result format is in the following example.

Example 1:

Input:

• Views table:

Output:

Solution

The problem is asking for finding users who viewed more than one article on a day. For this, you first need to aggregate the article_id per view_date and viewer_id. Notice that you need to find COUNT(DISTINCT article_id) because the same viewer might view the same article more than once like viewer_id=4.

You can first aggregate the results as a named subquery.

1WITH article_views AS (
2    SELECT viewer_id, view_date, COUNT(DISTINCT article_id) AS articles_viewed 
3        FROM Views
4        GROUP BY viewer_id, view_date
5)

From the output of this named query, you need to filter the viewer_ids which have more than one articles_viewed. Finally, you order the results by viewer_id.

1WITH article_views AS (
2    SELECT viewer_id, view_date, COUNT(DISTINCT article_id) AS articles_viewed 
3        FROM Views
4        GROUP BY viewer_id, view_date
5) SELECT DISTINCT viewer_id id
6    FROM article_views
7    WHERE articles_viewed > 1
8    ORDER BY id;

If you observe the usage of view_date and articles_viewed, they are not used at all in the output query. You could reduce above query by using HAVING clause to filter records having article views more than 1 as shown below.

1SELECT DISTINCT viewer_id id
2    FROM Views
3    GROUP BY viewer_id, view_date
4    HAVING COUNT(DISTINCT article_id) > 1
5    ORDER BY id;