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 authors who viewed at least one of their own posts. Sort the final output by their author_id 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:
author_id |
3 |
6 |
9 |
Explanation:
Only 3 authors were found in above input table who viewed their own post. And those are 3,6 and 9 .
SELECT * FROM post_views WHERE author_id = viewer_id ;
post_id | author_id | viewer_id | view_date |
33 | 6 | 6 | 2017-01-10 |
44 | 3 | 3 | 2017-09-20 |
44 | 3 | 3 | 2017-09-20 |
93 | 9 | 9 | 2017-09-21 |
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:
SELECT author_id FROM post_views WHERE author_id = viewer_id GROUP BY author_id ORDER BY author_id ASC ;