Unlocking SQL Secrets: Solving the 'Article Views' Puzzle with Precision

Recently, I solved LeetCode problem 1148 – "Article Views I", which involved identifying authors who viewed at least one of their own articles. Here’s a breakdown of the problem and the SQL solution, along with some insights.

Problem Recap

The Views table contains the following columns:

• author_id: ID of the author who wrote the article.
• viewer_id: ID of the person who viewed the article.

Our goal is to find all authors who have viewed their own articles. This happens when author_id is equal to viewer_id.

Initial Approach: Using JOIN

The first approach I tried was using a JOIN to find matching author_id and viewer_id. Here’s the query I used:

Create table Views(Id int Identity(1,1),author_id int,viewer_id int)

Insert into Views Values(3,5),(3,6),(7,7),(7,6),(7,1),(4,4),(4,4)

Select Distinct(V.author_id) From Views V Join Views V2 On V.author_id = V2.viewer_id

Select Distinct(V.author_id) From Views2 V Join Views2 V2 On V.author_id = V2.viewer_id

The actual correct result set was 5, 17, 55, but this query failed to return those values.

Why this doesn't always work:

• While this query can return correct results in some cases, it compares the author_id from one row with the viewer_id from another row, potentially leading to incorrect matches across rows. In scenarios with duplicate or unrelated rows, the query might incorrectly identify an author as having viewed their own article.

Optimized Solution: Row-by-Row Comparison

To ensure accuracy, I switched to a simpler and more reliable approach that checks if author_id equals viewer_id within the same row:

Select Distinct(author_id) From Views2 Where author_id = viewer_id

Why this works better:

• This query compares the author_id and viewer_id within the same row. It only returns authors who have viewed their own articles, ensuring an accurate match every time, regardless of duplicates or unrelated data.

Key Takeaways

1. Understanding JOIN behavior:
2. Row-by-Row Comparison with WHERE:

Conclusion

This problem helped highlight the importance of choosing the right SQL construct for the task at hand. While a JOIN can be useful in many situations, sometimes a straightforward WHERE condition gives a more accurate and efficient solution.

Have you encountered similar situations where using a JOIN didn't work as expected? I'd love to hear your thoughts!