Medium SQL Challenge: Using RANK() OR LIMIT

365 Days of Daily Coding: Day 36

I have been reading few pages a day in the morning lately. If you have been following me, I talked about my bad habits especially in the morning. Although I wake up on average at around 7.30 am , I do not wake up immediately. I just lie on my bed scrolling my insta. So what I am trying to do instead is to replace my scrolling habit with reading few pages.

I am currently reading “Thinking Fast and Slow” by Daniel Khaneman. This book is about pretty heavy as a subject but it is slowly growing into me. The book in general explains how our mind works and the biases that influences our decision making and choices.

A key point I remember from a chapter that I read in the book this morning is that people in public setting are less likely to help a needy individual than in a setting where nobody is around. It’s because when they are in a public setting they dispense the responsibility of helping the needy individual to others while in the latter situation they feel personally responsible and are more likely to help.

Today’s challenge was a medium challenge. I had to find out the first name and the last name of highest spending customer for the month of Feb. There were two tables: one had the customer_id and customer’s payment for each transaction while the other had personal information about the customer.

I had to join the table inorder to find the top paying customer’s first name and last name. Then, I had to aggregate the customer’s total payment amount. Below are two of my solutions:

Solution 1:

SELECT first_name, last_name FROM (
  SELECT c.first_name, c.last_name, c.customer_id, SUM(p.amount) AS amount
  FROM customer c LEFT JOIN payment p
  ON c.customer_id = p.customer_id
  WHERE EXTRACT(YEAR FROM payment_ts) = 2020 AND
  EXTRACT(MONTH FROM payment_ts) = 02
  GROUP BY c.customer_id
  ORDER BY amount DESC) AS k
LIMIT 1;

Solution 2:

WITH temp AS
  (
	SELECT customer_id, SUM(amount) AS total, 
	RANK() OVER(ORDER BY SUM(amount) DESC) AS rnk
	FROM payment
	WHERE EXTRACT(YEAR FROM payment_ts) = 2020 AND
  	EXTRACT(MONTH FROM payment_ts) = 02
	GROUP BY customer_id
  )
SELECT c.first_name, c.last_name 
FROM customer c LEFT JOIN temp t
ON c.customer_id = t.customer_id
WHERE t.rnk = 1;

I worked on two solutions because I figured out that with the first solution, I would not be able to identify the first name and last name of the other customers should there be a tie. With the second solution, using RANK(), I do not need to worry about other customers with the same rank being eliminated.

Karma

Leave a comment