Post Views – 1

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.

---

 

---

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
;