Medium SQL Challenge: Using IN()

365 Days of Daily Coding: Day 42

Learning a new skill is similar to learning a new sport where you are trying everything for the first time. It’s frustrating when you are unable to deliver the results smoothly. There’s obstacles particularly in the early phases that makes you question yourself and your intelligence. I have felt the same. I’d like to believe that it’s the perserverance that makes you ride the rough course.

I completed a chapter from a book that I have been reading. If you haven’t been following my daily blog, I am currently reading “Thinking Fast and Slow” by Daniel Kahneman. Today’s chapter was about “Illusions of Validity”. The key takeaway from the chapter was that there are several forces into play including blind luck that result in certain events and outcomes. Because of these hidden elements, nothing can be predicted to accuracy. In reality however, we have the political and economic pundits as well as stock traders that attribute their correct prediction as their skill and incorrect prediction to several excuses like “wrong timing”. These are not general statements that I or the book has made but are conclusions of scientific studies. This also supports some of the experts advise on why investing on index funds is a much better investment that picking individial stocks.

Well if everything I have achieved so far are the results of the forces such as blind luck, it feels as though that I can let the forces lead me instead without putting into much efforts. But I would like to believe that perserverance and hard work also plays a key role into the success of the results. This is a key point that the writer misses out.

Today, I solved a tricky medium challenge. I had to find the count of the customers who had rented movie atleast once in both May and June.

At first, I tried a query where I found the count of customers that rented in May and June. Below is the query:

SELECT COUNT(DISTINCT customer_id)
FROM rental
WHERE EXTRACT(YEAR FROM rental_ts) = 2020 
 AND EXTRACT(MONTH FROM rental_ts) IN (05, 06)

This is obviously incorrect because instead of finding the customers that rented both in May and June, what I was essentially doing was finding the sum of (customers that rented in May) + (customers that rented in June) and returning the distinct count.

My correct solution:

SELECT COUNT(DISTINCT customer_id)
FROM rental
WHERE EXTRACT(YEAR FROM rental_ts) = 2020 
AND EXTRACT(MONTH FROM rental_ts) = 05 
AND customer_id IN (SELECT customer_id FROM rental
			WHERE EXTRACT(YEAR FROM rental_ts)
                        = 2020 AND EXTRACT(
                         MONTH FROM rental_ts) = 06);

Karma

Leave a comment