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;