List Distinct Authors Who Viewed Their Own Articles
Company: Amazon
Role: Software Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Other
### Problem
Return each author who has viewed at least one article they wrote. Equal author_id and viewer_id mean the same person. Label the output id, remove repeated qualifying authors, and sort id ascending.
### Tables
Table: `Views`
```text
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| article_id | int |
| author_id | int |
| viewer_id | int |
| view_date | date |
+---------------+---------+
There is no primary key (column with unique values) for this table, the table may have duplicate rows.
Each row of this table indicates that some viewer viewed an article (written by some author) on some date.
Note that equal author_id and viewer_id indicate the same person.
```
### Example
```text
Views (article_id,author_id,viewer_id,view_date): (1,8,8,2021-01-01), (1,8,8,2021-01-01), (2,3,3,2021-01-02), (3,5,8,2021-01-03)
Output (id): (3), (8)
```
### What a Strong Answer Covers
- Identify self-views and include each qualifying author once.
- Return the requested column in ascending order.
### Follow-up Questions
- How would you count the number of distinct articles each author viewed?
- How would you report authors who never viewed an article they wrote?
Overview: Return each author who has viewed at least one article they wrote. Equal author_id and viewer_id mean the same person. Label the output id, remove repeated qualifying authors, and sort id ascending.
