365 Days of Daily Challenge: Day 62

Lately, Bit coins and cryptocurrency are in hype and their prices have been climbing massively. I have been tempted to invest some of my hard earned money but I dont know much about this industry and I don’t want to invest in something that I am not familiar with. Therefore, I have decided to study more on this topic everyday.

Today’s challenge was fairly challenging as it involved a lot more tables than I was working with.

Medium Challenge

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


SELECT c.name, SUM(t.revenue) AS revenue
FROM temp t JOIN film_category f
ON t.film_id = f.film_id
JOIN category c
ON f.category_id = c.category_id
GROUP BY c.name
ORDER BY revenue DESC
LIMIT 3;