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 | +------+
An efficient way to solve this problem is by performing a self-join on the Views table where the author_id is equal to the viewer_id. This will help in identifying rows where authors viewed their own articles. After identifying, we need to select distinct author IDs and return them in ascending order.
The solution involves selecting distinct authors whose author_id matches the viewer_id. The query returns all such instances, selects distinct ids, and orders them as required.
C
Time Complexity: O(n log n) - due to sorting the result.
Space Complexity: O(n) - storing distinct author IDs.
An alternative implementation can employ the use of a data structure such as a set to track those authors that viewed their own articles. We iterate over the Views table and whenever the author_id equates viewer_id, we insert it into the set. Finally, we convert this set into a sorted list of distinct author IDs.
The Python implementation makes use of sets to hold unique author_ids where the author is the viewer. This approach ensures that duplicates are automatically eliminated in an O(1) time complexity for insertions.
JavaScript
Time Complexity: O(n log n) - due to sorting the set elements.
Space Complexity: O(n) - to store the unique authors in memory.
| Approach | Complexity |
|---|---|
| Self-Join Approach | Time Complexity: O(n log n) - due to sorting the result. |
| Using Set Data Structure | Time Complexity: O(n log n) - due to sorting the set elements. |
4. Article Views 1 | SQL Interview Question and Answer #sqlforbeginners • Start Practicing • 8,333 views views
Watch 9 more video solutions →Practice Article Views I with our built-in code editor and test cases.
Practice on FleetCode