Article Views I - Solution & Explanation
Problem Statement
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 | +------+
Approach Overview
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 1: Self-Join Approach
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.
Complexity
Time Complexity: O(n log n) - due to sorting the result.
Space Complexity: O(n) - storing distinct author IDs.
Approach 2: Using Set Data Structure
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.
Code
Python
JavaScript
Complexity
Time Complexity: O(n log n) - due to sorting the set elements.
Space Complexity: O(n) - to store the unique authors in memory.
Approach 3: Default Approach
Code
MySQL
Complexity Comparison
| 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. |
| Default Approach | — |
Detailed Complexity Analysis
| 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 |
Video Solution
Article Views I | Leetcode 1148 | SQL_50 Study Plan | Crack SQL Interviews in 50 Qs #mysql • Learn With Chirag • 14,558 views views
Watch 9 more video solutions →Frequently Asked Questions
Is Article Views I easy or hard?
Article Views I Python/Java solution
How to solve Article Views I in O(n)?
What is the best approach for Article Views I?
Is Article Views I asked at Google/Amazon/Meta?
What data structure is used in Article Views I?
What is the time complexity of Article Views I?
Ready to solve this problem?
Practice Article Views I with our built-in code editor and test cases.
Practice on FleetCode