365 Days of Daily Coding: Day 68

It was a great day today. I managed to solve two sql challenges today and watched a fantastic movie called ‘Lion’ for a change. I have been watching Naruto, one of my favorite anime and the only anime that I watch.

I solved one medium challenge and one difficult challenge in sql pad. The difficult challenge took quite some time to solve but I managed to solve it anyway. I am really happy about that.

Medium Challenge:

WITH temp AS(
  SELECT i.film_id, SUM(p.amount) AS rev
  FROM inventory i LEFT JOIN rental r
  ON i.inventory_id = r.inventory_id
  LEFT JOIN payment p
  ON p.rental_id = r.rental_id
  GROUP BY i.film_id
  )
SELECT film_id, rev*100/(SELECT SUM(rev) FROM temp) AS revenue_percentage
FROM temp
WHERE film_id <= 10
ORDER BY film_id;

Difficult Challenge:

WITH temp AS 
(
 SELECT * FROM rental 
 WHERE EXTRACT(YEAR FROM rental_ts) = 2020 AND
 EXTRACT(MONTH FROM rental_ts) = 05
 ), temp2 AS 
 (
 SELECT i.film_id, i.inventory_id, t.rental_id 
FROM inventory i
LEFT JOIN temp t
ON i.inventory_id = t.inventory_id
)
SELECT COUNT(inventory_id)
FROM temp2
WHERE film_id NOT IN (SELECT DISTINCT film_id FROM temp2 WHERE rental_id IS NOT null)