1148. Article Views I - Solution & Explanation

The problem

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.

// Sample demonstration in pseudo-code
#include <stdio.h>
#include <stdlib.h>

int main() {
    // Assuming we have data in Views table loaded
    int views[][4] = {
        {1, 3, 5, 20190801},
        {1, 3, 6, 20190802},
        {2, 7, 7, 20190801},
        {2, 7, 6, 20190802},
        {4, 7, 1, 20190722},
        {3, 4, 4, 20190721},
        {3, 4, 4, 20190721}
    };
    int num_of_views = 7;
    int found_authors[100] = {0};
    int current_id;

    for (int i = 0; i < num_of_views; i++) {
        if (views[i][1] == views[i][2]) {
            current_id = views[i][1];
            found_authors[current_id] = 1;
        }
    }

    printf("+------+
| id   |
+------+
");
    for (int i = 0; i < 100; i++) {
        if (found_authors[i] == 1) {
            printf("| %d    |
", i);
        }
    }
    printf("+------+");
}

MySQL

SELECT DISTINCT V1.author_id as id FROM Views V1 WHERE V1.author_id = V1.viewer_id ORDER BY V1.author_id;

This is a conceptual approach to mimic the self join operation in C and to demonstrate the logic of matching author_id with viewer_id and filtering distinct. Since arrays with predefined size are used, it simplifies the lookup but in actual implementation, a dynamic structure would be needed.

Complexity

Time Complexity: O(n) - for iterating through the rows.
Space Complexity: O(1) - since space usage doesn't depend on input size, given a maximum author ID.

Try this approach in the editor

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.

Python

views = [
    [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']
]

unique_authors = set()
for view in views:
    article_id, author_id, viewer_id, view_date = view
    if author_id == viewer_id:
        unique_authors.add(author_id)

print(sorted(unique_authors))

JavaScript

let views = [
    { article_id: 1, author_id: 3, viewer_id: 5, view_date: '2019-08-01' },
    { article_id: 1, author_id: 3, viewer_id: 6, view_date: '2019-08-02' },
    { article_id: 2, author_id: 7, viewer_id: 7, view_date: '2019-08-01' },
    { article_id: 2, author_id: 7, viewer_id: 6, view_date: '2019-08-02' },
    { article_id: 4, author_id: 7, viewer_id: 1, view_date: '2019-07-22' },
    { article_id: 3, author_id: 4, viewer_id: 4, view_date: '2019-07-21' },
    { article_id: 3, author_id: 4, viewer_id: 4, view_date: '2019-07-21' }
];

let authorSet = new Set();
views.forEach(view => {
    if (view.author_id === view.viewer_id) {
        authorSet.add(view.author_id);
    }
});

console.log(Array.from(authorSet).sort((a, b) => a - b));

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.

Complexity

Time Complexity: O(n log n) - due to sorting the set elements.
Space Complexity: O(n) - to store the unique authors in memory.

Try this approach in the editor

Complexity comparison

Approach	Complexity
Self-Join Approach	Time Complexity: O(n) - for iterating through the rows. Space Complexity: O(1) - since space usage doesn't depend on input size, given a maximum author ID.
Using Set Data Structure	Time Complexity: O(n log n) - due to sorting the set elements. Space Complexity: O(n) - to store the unique authors in memory.

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

Frequently asked questions

Is Article Views I easy or hard?

Article Views I is classified as an Easy database problem on LeetCode with a high acceptance rate around 76%. The challenge mainly tests whether you can translate a simple condition into a SQL query and return unique sorted results.

Article Views I Python/Java solution

In Python or JavaScript, iterate through each view record and check whether author_id equals viewer_id. Insert matching authors into a set to maintain uniqueness, then convert the set into a sorted list before returning the result.

How to solve Article Views I in O(n)?

Iterate through every record in the Views table and check if author_id == viewer_id. If the condition is true, add the author to a distinct result set. In SQL, this translates to SELECT DISTINCT author_id FROM Views WHERE author_id = viewer_id ORDER BY author_id.

What is the best approach for Article Views I?

The optimal approach is filtering rows where author_id equals viewer_id and returning distinct author IDs. In SQL this is done with a WHERE condition and DISTINCT clause. The database scans the table once, resulting in O(n) time complexity and constant additional space.

Is Article Views I asked at Google/Amazon/Meta?

Article Views I is a common introductory database question similar to screening tasks used in SQL interviews. Variations of filtering rows and deduplicating results frequently appear in interviews at large tech companies including Amazon, Google, and Meta for data or backend roles.

What data structure is used in Article Views I?

In SQL, the solution relies on relational filtering and DISTINCT operations rather than explicit data structures. In programming languages like Python or JavaScript, a hash-based set is typically used to store unique author IDs while scanning the records.

What is the time complexity of Article Views I?

The problem can be solved in O(n) time where n is the number of rows in the Views table. The algorithm performs a single pass to check whether author_id equals viewer_id. Space complexity is O(1) in SQL or O(k) when storing results in a set in languages like Python or JavaScript.

Article Views I solution and explanation

The problem

Approach overview

Approach 1: Self-Join Approach

Approach 2: Using Set Data Structure

Complexity comparison

Complexity analysis

Frequently asked questions

Comments

Article Views I solution and explanation

The problem

Approach overview

Approach 1: Self-Join Approach

Approach 2: Using Set Data Structure

Complexity comparison

Complexity analysis

Frequently asked questions

Comments

Related problems