It’s an easy problem from SQL 50 with the description being:
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 solution to find all the authors that viewed at least one of their own articles.
Return the result table sorted by id in ascending order.
The result format is in the following example.
Example 1:
Input:
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 |
+------+
A way to know if the author is the viewer is basically comparing both in the conditions.
The output requested is the “all the authors that viewed at least one of their own articles.” since you are comparting both either way would be fine, also the column in the output needs an alias as id, which can be done using AS.
They don’t want duplicated ids in the output, to get sorted out use of distinct is a great way to avoid duplications.
And last but not least, the order needs to be ascending so using order by asc (order by is asc by default but you can add if you want) would get this done as expected:
SELECT DISTINCT(viewer_id) AS id FROM Views WHERE viewer_id = author_id ORDER BY id ASC;
OR
SELECT DISTINCT author_id AS id FROM Views WHERE viewer_id = author_id ORDER BY id;
Runtime: 815 ms, faster than 77.65% of MySQL online submissions for Article Views I.
Memory Usage: 0B, less than 100.00% of MySQL online submissions for Article Views I.
That’s it!
If there is anything thing else to discuss feel free to drop a comment, if I missed anything let me know so I can update accordingly.
Until next post! :)
Top comments (0)