LeetCode Problem 1148: Article Views I - SQL Solution Explained

Problem Overview

The problem "1148. Article Views I" asks to find the distinct authors whose articles were viewed by themselves. In this case, we are given two columns:

• author_id: The ID of the article's author.
• viewer_id: The ID of the viewer who viewed the article.

The task is to return the distinct author_id from the Views table where the author_id is the same as the viewer_id. This will help us identify authors who viewed their own articles.

Step-by-Step Solution

1. Using SELECT DISTINCT: We start by using SELECT DISTINCT to retrieve unique author_ids from the Views table. The DISTINCT keyword ensures that duplicate entries are eliminated, leaving us with only distinct author_ids.

2. Condition to Identify Self-Views: The key condition here is to check if the author_id is equal to the viewer_id. This helps us find the authors who have viewed their own articles. We specify this condition with WHERE author_id = viewer_id.

3. Sorting the Results: To organize the output, we add ORDER BY author_id. This step sorts the result set by the author_id in ascending order.

SQL Query:

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

• SELECT DISTINCT: Fetches unique author IDs.
• FROM Views: Specifies the Views table as the source.
• WHERE author_id = viewer_id: Filters the results where the author viewed their own article.
• ORDER BY author_id: Sorts the results by author_id in ascending order.

Conclusion

This SQL query efficiently finds the authors who viewed their own articles, ensuring we get a clean, sorted result of unique author_ids. The combination of SELECT DISTINCT, the condition author_id = viewer_id, and ordering helps us achieve the desired output in an optimal way.

#LeetCode 1148 SQL solution #SQL query for article views #Find authors who viewed their own articles #SQL SELECT DISTINCT with WHERE clause #LeetCode SQL problem solution