Article Views I - Practice Coding Problems

Article Views I - Problem

Database Easy

Welcome to the world of content analytics! 📊

You're working as a data analyst for a popular online publishing platform. Your task is to identify narcissistic authors - those who read their own articles! 📝

Given a Views table that tracks every article view, you need to find all authors who have viewed at least one of their own articles.

Table Structure:

Column Name	Type
article_id	int
author_id	int
viewer_id	int
view_date	date

Key Points:

🔄 The table may contain duplicate rows (no primary key)
👤 When author_id equals viewer_id, it means the author viewed their own article
📈 Return the result sorted by id in ascending order

Goal: Find all unique author IDs who have been caught reading their own content!

Input & Output

example_1.sql — Basic Case

$ Input: Views table: +------------+-----------+-----------+------------+ | article_id | author_id | viewer_id | view_date | +------------+-----------+-----------+------------+ | 1 | 3 | 5 | 2019-08-01 | | 1 | 3 | 3 | 2019-08-01 | | 2 | 7 | 7 | 2019-08-01 | | 2 | 7 | 6 | 2019-08-02 | +------------+-----------+-----------+------------+

› Output: +------+ | id | +------+ | 3 | | 7 | +------+

💡 Note: Author 3 viewed their own article (article 1), and author 7 viewed their own article (article 2). Author 3 and 7 are the narcissistic authors!

example_2.sql — With Duplicates

$ Input: Views table: +------------+-----------+-----------+------------+ | article_id | author_id | viewer_id | view_date | +------------+-----------+-----------+------------+ | 1 | 3 | 3 | 2019-08-01 | | 1 | 3 | 3 | 2019-08-01 | | 2 | 5 | 8 | 2019-08-02 | +------------+-----------+-----------+------------+

› Output: +------+ | id | +------+ | 3 | +------+

💡 Note: Even though author 3 has duplicate self-view records, DISTINCT ensures they appear only once in the result.

example_3.sql — No Self-Views

$ Input: Views table: +------------+-----------+-----------+------------+ | article_id | author_id | viewer_id | view_date | +------------+-----------+-----------+------------+ | 1 | 3 | 5 | 2019-08-01 | | 2 | 7 | 6 | 2019-08-02 | | 3 | 4 | 1 | 2019-08-03 | +------------+-----------+-----------+------------+

› Output: Empty result set (no rows)

💡 Note: No author viewed their own article, so the result is empty. All authors were modest!

Visualization

Tap to expand

Understanding the Visualization

Examine the Logbook

Look at each entry in the Views table - it shows article_id, author_id, viewer_id, and view_date

Spot the Pattern

When author_id equals viewer_id, you've caught an author reading their own work!

Collect Evidence

Gather all unique author IDs who appear as both author and viewer

Present Findings

Sort the caught authors by ID and present your findings

Key Takeaway

🎯 Key Insight: This is a simple filtering problem! Just look for rows where `author_id = viewer_id` - no complex joins needed. The DISTINCT keyword handles duplicates, and ORDER BY sorts the results.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

O(n) to scan the table + O(k log k) to sort results where k is number of unique authors

⚡ Linearithmic

Space Complexity

O(k)

Space for storing unique author IDs (k is much smaller than n)

✓ Linear Space

Constraints

1 ≤ Views table rows ≤ 10⁵
1 ≤ article_id, author_id, viewer_id ≤ 10⁹
view_date is a valid date
The table may contain duplicate rows

Asked in

G Google 15 a Amazon 12 f Meta 8 ⊞ Microsoft 6

The optimal solution uses a simple SQL WHERE clause to filter rows where author_id = viewer_id. Combined with DISTINCT to handle duplicates and ORDER BY for sorting, this approach achieves O(n) time complexity with minimal space usage.

Common Approaches

Approach	Time	Space	Notes
✓ Single Pass Filter (Optimal)	O(n log n)	O(k)	Directly filter rows where author_id equals viewer_id in one scan
Brute Force SQL (Cartesian Join)	O(n²)	O(n)	Join the table with itself to compare all combinations

Single Pass Filter (Optimal) — Algorithm Steps

Scan each row in the Views table
Check if author_id equals viewer_id
Collect matching author IDs
Remove duplicates using DISTINCT
Sort results in ascending order

Visualization

Tap to expand

Step-by-Step Walkthrough

Scan Each Row

Go through each record in the Views table

Check Condition

Compare author_id with viewer_id

Collect Matches

Add matching author IDs to result set

Sort & Return

Sort unique author IDs in ascending order

Code -

solution.c — C

// Optimal SQL Query
/*
SELECT DISTINCT author_id as id
FROM Views 
WHERE author_id = viewer_id
ORDER BY id ASC;
*/

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

struct ViewRecord {
    int article_id;
    int author_id;
    int viewer_id;
    char view_date[20];
};

int compare(const void *a, const void *b) {
    return (*(int*)a - *(int*)b);
}

int* findAuthorsOptimal(struct ViewRecord* views_data, int n, int* return_size) {
    int* result = (int*)malloc(n * sizeof(int));
    int unique_authors[1000] = {0}; // Assuming author IDs < 1000
    int count = 0;
    
    // Single pass through the data
    for (int i = 0; i < n; i++) {
        if (views_data[i].author_id == views_data[i].viewer_id) {
            if (!unique_authors[views_data[i].author_id]) {
                unique_authors[views_data[i].author_id] = 1;
                result[count++] = views_data[i].author_id;
            }
        }
    }
    
    // Sort the result
    qsort(result, count, sizeof(int), compare);
    *return_size = count;
    return result;
}

int main() {
    struct ViewRecord sample_views[] = {
        {1, 3, 5, "2019-08-01"},
        {1, 3, 3, "2019-08-01"},
        {2, 7, 7, "2019-08-01"},
        {2, 7, 6, "2019-08-02"}
    };
    
    int return_size;
    int* result = findAuthorsOptimal(sample_views, 4, &return_size);
    
    printf("[");
    for (int i = 0; i < return_size; i++) {
        printf("%d", result[i]);
        if (i < return_size - 1) printf(", ");
    }
    printf("]\n"); // Output: [3, 7]
    
    free(result);
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

O(n) to scan the table + O(k log k) to sort results where k is number of unique authors

⚡ Linearithmic

Space Complexity

O(k)

Space for storing unique author IDs (k is much smaller than n)

✓ Linear Space

Constraints

1 ≤ Views table rows ≤ 10⁵
1 ≤ article_id, author_id, viewer_id ≤ 10⁹
view_date is a valid date
The table may contain duplicate rows

23.4K Views

High Frequency

~8 min Avg. Time

890 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Single Pass Filter (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler