365 Days of Daily Challenge: Day 80

Today, I solved a difficult SQL challenge that I had been unable to solve previously. It took a good amount of time to debug what was not working. I finally managed to figure out that the NULL records when using GROUP BY are treated as a separate groups. I used the coalesce function to treat those nulls.

Difficult SQL Challenge:

WITH temp AS
(SELECT f.film_id, SUM(p.amount) AS amt
FROM film f LEFT JOIN inventory i
ON f.film_id = i.film_id
LEFT JOIN rental r
ON i.inventory_id = r.inventory_id
LEFT JOIN payment p
ON r.rental_id = p.rental_id
GROUP BY f.film_id)


SELECT film_group, COUNT(*)
FROM(
SELECT CASE 
WHEN amt >= 100 THEN 'high'
WHEN amt < 20 THEN 'low'
ELSE 'medium' END AS film_group
FROM( 
SELECT film_id, COALESCE(amt, 0) AS amt
FROM temp) AS k) AS l
GROUP BY film_group;