LeetCode-1148 Article Views I (Easy)

Problem:

Here we are given a Views table with schema -
+ — — — — — — — -+ — — — — -+
| Column Name | Type |
+ — — — — — — — -+ — — — — -+
| article_id | int |
| author_id | int |
| viewer_id | int |
| view_date | date |
+ — — — — — — — -+ — — — — -+
- There is no Primary Key which means there can be duplicate rows.
- Equal author_id and viewer_id represents same person.

Our Task:

We have to find all the authors who have viewed atleast one of their own articles. Return the result table sorted by id in ascending order.
Sample Output —
+ — — — +
| id |
+ — — — +
| 4 |
| 7 |
+ — — — +

Get MXCode’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Final Query —

SELECT DISTINCT(author_id) AS id
FROM Views
WHERE author_id = viewer_id
ORDER BY author_id;

Approach —

• We are interested only in the rows where author has viewed their own article, i.e., author_id = viewer_id.
• Since the Views table can have duplicates, we will use DISTINCT keyword to avoid repeating ids in output.
• We rename author_id as id using the AS (Alias).
• Finally, we sort the output by author_id using ORDER BY.
  NOTE — ORDER BY sorts in ascending by default, so no need to write ASC.