Data structures and Algorithms Day 2 / 100

LeetCode 584. Find Customer Referee

The Problem

Find the names of the customer that are not referred by the customer with id = 2.

Return the result table in any order. Table example

The logic

Very simple, create a query that finds all names from the Customer table where the referee_id is not 2 but anything else. The trick here is that there are null values and integers on the table so the WHERE filter needs to consider that. So I came up with this.

First try: My solution as self taught Data worker

SELECT name
FROM Customer
WHERE NOT (referee_id = 2) OR referee_id IS NULL;

Breakdown and Explanation: First try

1. SELECT name: This part tells the computer that we want to look at information related to the names of customers. Think of it as asking the computer to only show you the column with names in a big spreadsheet.

2. FROM Customer: Here, we are telling the computer where to find the information. The Customer part refers to a specific table (like a specific spreadsheet) that has all the information about customers.

3. WHERE: This part is where we set the rules or conditions to filter the information. We only want certain rows (lines) of information that meet specific criteria.

   • NOT (referee_id = 2): This condition tells the computer to exclude any row where the "referee_id" is equal to 2. You can think of "referee_id" as a column that tells us who referred the customer. The NOT part means we want all the rows except those where this column is equal to 2.

   • OR referee_id IS NULL: This additional condition is combined with the previous one using the word OR. It tells the computer to also include rows where the "referee_id" is missing or empty (represented by NULL in SQL).

The Code snippet was accepted and ran thru the tests but it felt relatively slow for my liking and the results where devastating..

Failing forwards = Learning: Second try

As the results were horrible even though accepted fully, I wanted to see why the query was getting destroyed so started looking and found an interesting and new to me way to look into this problem <>. DIscussions on the internet lead to believe that depending on the way the database is built occasionally using <> might be faster so I gave it a try...and well took me several fails before finding the correct way to build it which was this

SELECT name
FROM Customer
WHERE referee_id <> 2 OR referee_id IS NULL;

And the test felt faster so I gave it a go:

Well, at least I was beating half the field now and learned something. Next time I build queries on our database I will have to remember testing <>.

Then I realized that I work with MS SQL Server at work and LeetCode Offers that too so from here on out I might have to stick to that instead. And was I in for a surprise..

That <> was performing horribly while...

..my original solution was almost crushing it.

Conclusion

In conclusion, optimizing SQL queries can be a complex and challenging task that requires experimentation with various approaches and techniques. It is crucial to understand the specific database system being used, as this can have a significant impact on the performance of the query. By testing different methods, such as using the NOT operator or the <> symbol, you can identify the most efficient solution for your particular use case. Furthermore, paying attention to details like handling NULL values and understanding the nuances of different database systems, like MS SQL Server, can lead to substantial improvements in query performance. Overall, the process of refining SQL queries not only enhances your skills as a data worker but also contributes to more efficient and effective data analysis.

Main takeaways and learning

Learned a new way to run a query

Learned how it seemingly actually matters what system you are running your SQL Queries on.

Didn't learn anything new concept-wise.

So far it has been fun instead horror...but I am in my SQL comfort zone at the moment.

Went up like 600k+ in ranks