365 Days of Gratitude: Day 74

Gosh I am soo tired today. I was trying to solve a difficult challenge that I had skipped in the past. I would say I almost got the answer because it nearly solves the challenge. I will try again some day. Meanwhile, the medium challenge that I solved today was one of those challenges that I had been unable to solve a few days ago. And that makes me elated.

That also marks completion of me wanting to solve atleast two challenges.

Medium SQL Challenge:

WITH temp1 AS
(SELECT f.film_id, c.category_id, c.name
FROM film_category f JOIN category c
ON f.category_id = c.category_id),
temp2 AS
(SELECT i.film_id, r.rental_id, r.inventory_id
 FROM rental r JOIN inventory i
 ON r.inventory_id = i.inventory_id
), temp3 AS 
(SELECT t.film_id, t.inventory_id, t.rental_id, p.amount 
FROM(
from temp2 t JOIN payment p
ON t.rental_id = p.rental_id
ORDER BY inventory_id),
temp4 AS 
(SELECT film_id, SUM(amount) AS amount
FROM temp3
GROUP BY film_id
ORDER BY film_id)

Difficult Challenge:

SELECT * FROM(
SELECT film_id, name, amount * 100 /SUM(amount) OVER (PARTITION BY name)
FROM(
SELECT t.film_id, s.name, t.amount
FROM temp4 t JOIN temp1 s
ON t.film_id = s.film_id) AS k) AS l
WHERE film_id <= 10