Post Views – 2

Post views are being analysed using post_views table. This table contains information of all views on each posts date wise.
Task is to find all viewers (viewer_id) who viewed more than one post on same date. Sort the final output in ascending order.


 

Input Table: post_views

post_id author_id viewer_id view_date
22 2 4 2017-01-10
44 3 4 2017-01-10
22 2 5 2017-01-20
33 6 6 2017-01-10
33 6 5 2017-01-20
55 6 1 2017-09-21
44 3 3 2017-09-20
44 3 3 2017-09-20
93 9 9 2017-09-21

Output Table:

viewer_id
4
5

Explanation:

Only 2 viewers were found in above input table who viewed more than one post on same date. And those are 4 and 5.

SELECT 
    A.view_date, A.viewer_id, COUNT(*) AS Counts
FROM
    (SELECT 
        view_date, viewer_id, post_id
    FROM
        post_views
    GROUP BY view_date , viewer_id , post_id) A
GROUP BY A.view_date , A.viewer_id
ORDER BY A.view_date;
view_date viewer_id Counts
2017-01-10 4 2
2017-01-10 6 1
2017-01-20 5 2
2017-09-20 3 1
2017-09-21 1 1
2017-09-21 9 1

DDLs:

CREATE TABLE IF NOT EXISTS post_views (
    post_id INT,
    author_id INT,
    viewer_id INT,
    view_date DATE
);
INSERT INTO post_views VALUES (22, 2, 4, '2017-01-10');
INSERT INTO post_views VALUES (44, 3, 4, '2017-01-10');
INSERT INTO post_views VALUES (22, 2, 5, '2017-01-20');
INSERT INTO post_views VALUES (33, 6, 6, '2017-01-10');
INSERT INTO post_views VALUES (33, 6, 5, '2017-01-20');
INSERT INTO post_views VALUES (55, 6, 1, '2017-09-21');
INSERT INTO post_views VALUES (44, 3, 3, '2017-09-20');
INSERT INTO post_views VALUES (44, 3, 3, '2017-09-20');
INSERT INTO post_views VALUES (93, 9, 9, '2017-09-21');

Solution – 1:

SELECT 
    B.viewer_id
FROM
    (SELECT 
        A.view_date, A.viewer_id
    FROM
        (SELECT 
        view_date, viewer_id, post_id
    FROM
        post_views
    GROUP BY view_date , viewer_id , post_id) A
    GROUP BY A.view_date , A.viewer_id
    HAVING COUNT(*) > 1) B
GROUP BY B.viewer_id
;

Solution – 2:

SELECT 
    DISTINCT viewer_id
FROM
    post_views
GROUP BY viewer_id , view_date
HAVING COUNT(DISTINCT post_id) > 1
ORDER BY 1;

 

Leave a Comment