Medium SQL Challenge: Using RANK()

365 Days of Daily Coding: Day 37

I finished a chapter from the book that I am currently reading. The chapter was about predictions and our biases that influences predctions. A key point in the chapter was the idea of “Regression to the mean”. In simple words, it means that if a footballer played the best for the season, s/he will most likely perform lower in the next season because s/he may have had an upperhand which you may call luck or best shape when they performed the best. It is not easy to maintain such upperhand in every season and as a result their performance may lower regressing their overall performance towards their average. If I could translate this to my work, I would apply it on my work for revenue forecasting. I would look at their average (called base rate in probability), evaluate evidences based on signifiers like good economy to raise every points above or lower my average.

Today, I completed a medium challenge. I had to find out the first name and last name of the individual who rented the most during the month of May. It is quite similar to the challenge I completed yesterday. This challenge als provided me the opportunity to solve the challenge in another way.

My soluion:

SELECT first_name, last_name FROM(
SELECT c.first_name, c.last_name, 
RANK() OVER(ORDER BY COUNT(r.rental_id) DESC) AS rnk
FROM customer c LEFT JOIN rental r
ON c.customer_id = r.customer_id
WHERE EXTRACT(YEAR FROM rental_ts) = 2020 AND
EXTRACT (MONTH FROM rental_ts) = 05
GROUP BY c.customer_id) AS k
WHERE rnk = 1;

Karma

Leave a comment