Watch 10 video solutions for Article Views I, a easy level problem involving Database. This walkthrough by Learn With Chirag has 12,953 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
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 | +------+
Problem Overview: The Views table records article views with article_id, author_id, and viewer_id. The task is to return all authors who viewed at least one of their own articles. The result should contain distinct author IDs sorted in ascending order.
Approach 1: Self-Join / Self-Filter (O(n) time, O(1) space)
This database problem is essentially a filtering task. You scan the Views table and select rows where the author_id is the same as the viewer_id. That condition directly identifies cases where an author viewed their own article. In SQL, you can implement this either with a simple WHERE author_id = viewer_id filter or with a self-join where both sides represent the same table. After filtering, return DISTINCT author_id and sort the result. The database performs a single pass over the rows, giving O(n) time complexity and constant additional space.
This approach is the most natural solution when working with SQL. The key insight is that the problem does not require comparing different rows—only checking a relationship between two columns within the same row.
Approach 2: Using Set Data Structure (O(n) time, O(k) space)
When solving outside SQL (for example in Python or JavaScript), treat the dataset as a list of records. Iterate through each view entry and check whether author_id == viewer_id. Whenever the condition holds, insert the author ID into a set. A set automatically removes duplicates, so you don’t need extra logic for uniqueness.
After processing all rows, convert the set to a sorted list and return it. The iteration step costs O(n) time. Insertions into a hash-based set take O(1) on average, and the extra memory depends on the number of qualifying authors k, giving O(k) space complexity.
Recommended for interviews: The SQL filtering approach is what interviewers expect for a database question. It demonstrates that you recognize the direct column comparison and can use DISTINCT and sorting efficiently. The set-based approach is useful when solving with general-purpose languages, showing you understand how to enforce uniqueness with hash-based data structures.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Self-Join / SQL Filter | O(n) | O(1) | Best for SQL queries or database interview problems where filtering rows directly is sufficient |
| Set Data Structure | O(n) | O(k) | When implementing the logic in Python, JavaScript, or other languages outside SQL |
