Article Views I | LeetCode 1148 | SQL_50 Study Plan

Welcome to the fourth question of the Crack SQL Interview in 50 Questions series. In this chapter, we explore LeetCode 1148: Article Views I, examining how to filter relational data based on column equality, remove duplicates, alias column headers, and sort results in ascending order using standard SQL keywords.

1. Why Do We Need This? (Problem Statement & Status Quo)

In modern web applications, platforms track user interactions, such as who authored an article and who viewed it. Data analysts and engineering teams frequently need to query activity logs to surface specific behavioral patterns.

For this problem, the goal is to identify all authors who have viewed at least one of their own articles. This requires comparing multiple columns within the same row, filtering out noise, ensuring distinct records, and presenting the output in a clean, sorted format.

2. Core Schema & Mental Model

The problem provides a single table named Views containing four columns.

Schema Breakdown:

• article_id (Integer): The unique identifier of the article.
• author_id (Integer): The unique identifier of the user who wrote the article.
• viewer_id (Integer): The unique identifier of the user who viewed the article.
• view_date (Date): The date when the view occurred.

Note: There is no primary key for this table, which means the table may contain duplicate rows. Furthermore, a crucial business rule given in the prompt states: Equal author_id and viewer_id indicate the same person.

3. Real-World Analogy: Self-Review Logs

Imagine a publishing house where writers write articles, and anybody—including the writers themselves—can read them.

If you want to audit how often writers review their own work, you look at a visitor log sheet. For every single row (entry) in that log, you check two specific columns: Who wrote it? (author_id) and Who read it? (viewer_id). If both names match, it means the author viewed their own publication. We then extract those unique author IDs and arrange them neatly in alphabetical or numerical order.

4. Naive vs. Better SQL Approach

When tackling this problem, developers can fall into traps regarding duplicate rows or unformatted column headers.

5. Step-by-Step Evolution of the Query

Let us build the SQL query piece by piece based on the requirements:

Step A: Selecting the Column and Handling Duplication

Since an author might view their own articles multiple times, we must ensure each author ID appears only once in our result set. We achieve this using the DISTINCT keyword.

SELECT DISTINCT author_id
FROM Views;

Step B: Renaming the Column Header

The problem explicitly states that the output column must be named id. In SQL, we use the AS keyword to alias columns.

SELECT DISTINCT author_id AS id
FROM Views;

Step C: Filtering for Self-Views

We only care about rows where the author viewed their own article. This means author_id must equal viewer_id. We use a WHERE clause for this condition:

SELECT DISTINCT author_id AS id
FROM Views
WHERE author_id = viewer_id;

Step D: Sorting the Results

Finally, the output must be sorted in ascending order by the author ID. We apply the ORDER BY keyword.

SELECT DISTINCT author_id AS id
FROM Views
WHERE author_id = viewer_id
ORDER BY id ASC;

(Note: ASC is the default sorting behavior in SQL, so writing ORDER BY id alone is completely sufficient).

6. Complete Flow Walkthrough

Let's trace how the final SQL query executes against the database engine:

7. Final Clean SQL Solution

Here is the final, production-ready solution submitted and accepted on LeetCode:

SELECT DISTINCT author_id AS id
FROM Views
WHERE author_id = viewer_id
ORDER BY id;

Summary and Key Takeaways

• Row-Level Column Comparison: Use the WHERE clause to compare two columns within the same row (e.g., WHERE author_id = viewer_id).
• Eliminating Duplicates: Use the DISTINCT keyword right after SELECT to ensure unique records when multiple identical rows exist.
• Column Aliasing: Use the AS keyword (author_id AS id) to rename output columns to match strict problem specifications.
• Sorting Data: Use the ORDER BY keyword to arrange query results in ascending (ASC) or descending (DESC) order.
• Default Ordering: ORDER BY defaults to ascending order when no explicit sorting modifier is provided.
• No Primary Key Awareness: Always anticipate duplicate records when dealing with tables that lack a primary key constraint.