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.

---

 

---

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;