Article Views I

Table: Views

+---------------+---------+
| 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.

Write a SQL query to find all the authors that viewed at least one of their own articles.

Return the result table sorted by id in ascending order.

For example:

Views table:
+------------+-----------+-----------+------------+
| 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    |
+------+

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

Explanation:

1. SELECT DISTINCT author_id AS id: This selects the distinct author_id values from the Views table and aliases them as id. The DISTINCT keyword ensures that each author ID appears only once in the result.

2. FROM Views: This specifies that we are querying the Views table.

3. WHERE author_id = viewer_id: This is the core logic of the query. It filters the rows of the Views table to include only those rows where the author_id is equal to the viewer_id. This condition identifies the authors who have viewed their own articles.

4. ORDER BY id ASC: This sorts the result set in ascending order based on the id (which is the author_id).

Alternative solution using GROUP BY and HAVING:

SELECT author_id AS id
FROM Views
GROUP BY author_id
HAVING COUNT(CASE WHEN author_id = viewer_id THEN 1 END) > 0
ORDER BY id ASC;

Explanation:

1. Selects the distinct author_id and aliases it as id from the table Views
2. Group by the author_id
3. Filter such that we only have author_id that are equal to viewer_id, so we know the author viewed at least one of their own articles.