SQL Interview Questions – Article Views

Problem Description –

Write an SQL query 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 query result format is in the following example.

Difficulty Level – Medium

Problem Link – Article Views

Solution –

SELECT
    DISTINCT viewer_id as id
FROM (
    SELECT
        view_date,
        viewer_id,
        COUNT(DISTINCT article_id) as total_views
    FROM views
    GROUP BY 1, 2 
    ) x
WHERE total_views > 1
ORDER BY 1

Here for each date we need to find out how many unique article is viewed by each person. Then we need to select only people who viewed more than one article on the same day.