SQL Solutions: A Guide to Efficient Queries and Good Coding Habits

It is day 4 of my #100DaysOfDSA challenge and this time we'll explore LeetCode SQL50 solutions to find authors who viewed at least one of their own articles 1148. Article Views I. We'll look at efficient querying techniques, why ordering results can be essential, and how good coding habits play a role.

The Problem

We have a Views table with the following structure:

Our objective is to find all authors that viewed at least one of their articles.

Using WHERE and DISTINCT (Recommended Approach)

The optimal solution for this task is to filter the rows where the author ID is equal to the viewer ID. This was also the solution I wrote first and fits the logical way I approach these problems.

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

Breakdown

• The WHERE clause filters only the rows where the author is the viewer.

• The DISTINCT keyword ensures that the result contains unique author IDs.

• The ORDER BY clause orders the results by author ID.

Using JOIN and DISTINCT

An alternative, more complex approach is to JOIN the table with itself. The results are not a thing to brag about.

SELECT DISTINCT v1.author_id AS id
FROM Views AS v1
JOIN Views AS v2 ON v1.article_id = v2.article_id
WHERE v1.author_id = v2.viewer_id
ORDER BY v1.author_id;

Breakdown

• The JOIN operation combines the table with itself.

• The WHERE clause filters rows with matching author and viewer IDs.

• The DISTINCT keyword ensures unique results.

• The ORDER BY clause orders the results by author ID.

Comparison and Insights

Performance and Readability

Solution 1 is more performant and readable, making it the preferred approach.

The ORDER BY Clause: A Good Coding Habit

The ORDER BY clause, used in both solutions, ensures that the results are sorted by author ID in ascending order. While in smaller datasets, SQL may return results in the expected order without specifying ORDER BY, it's not guaranteed.

Choosing to use ORDER BY ensures consistent results regardless of data size or underlying database changes. It represents a good coding habit that adds clarity to the intended result and protects against unexpected ordering issues. One has to also adapt to the SQL they are using, for example, MS SQL doesn't need the ORDER BY clause because it puts results in ascending order by default but MySQL doesn't.

Conclusion

This guide offers two solutions for identifying authors who viewed their own articles, emphasizing the efficient and concise use of SQL queries. By employing good coding habits like the use of the ORDER BY clause, developers can create more robust and reliable queries.

Understanding these concepts empowers developers to build effective data-driven applications and platforms. Whether you're a beginner or a seasoned SQL professional, embracing these practices will elevate your querying skills. Happy querying!