Article Views - Authors Who Viewed Their Own Articles
Preview mode. Log in to edit, run, submit, and save progress.
Description
You are given a Views table that logs which viewer read which article on a given date. There is no primary key, so duplicate rows can exist. Write a SQL query to find all authors who have viewed at least one of their own articles - i.e., rows where author_id = viewer_id. Return the result as id, sorted in ascending order.
Database Schema
Views
| Column Name | Example Value |
|---|---|
| article_id | 1 |
| author_id | 3 |
| viewer_id | 5 |
| view_date | 2019-08-01 |
Example
Views
| article_id | author_id | viewer_id | view_date |
|---|---|---|---|
| 1 | 3 | 5 | 2019-08-01 |
| 1 | 3 | 6 | 2019-08-02 |
| 2 | 7 | 7 | 2019-08-01 |
| 2 | 7 | 6 | 2019-08-02 |
| 4 | 7 | 1 | 2019-07-22 |
| 3 | 4 | 4 | 2019-07-21 |
| 3 | 4 | 4 | 2019-07-21 |
Output
| id |
|---|
| 4 |
| 7 |
Explanation:
Author 7 viewed their own article (article_id=2). Author 4 viewed their own article (article_id=3) - the duplicate row is ignored by DISTINCT. Author 3 never viewed their own articles.
Approach hint
Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.
Common mistake
Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.
Views
| article_id | author_id | viewer_id | view_date |
|---|---|---|---|
| 1 | 3 | 5 | 2019-08-01 |
| 1 | 3 | 6 | 2019-08-02 |
| 2 | 7 | 7 | 2019-08-01 |
| 2 | 7 | 6 | 2019-08-02 |
| 4 | 7 | 1 | 2019-07-22 |
| 3 | 4 | 4 | 2019-07-21 |
| 3 | 4 | 4 | 2019-07-21 |
Output
| id |
|---|
| 4 |
| 7 |