365 Days of Coding Challenge: Day 75

I went out with my friends today. I had a really good time despite all the waitings. It’s good to go outside when you have been couped up at home really long. Some people be like “Covid cases are increasing. Do your part by staying at home.” That makes me wonder if they will take responsibility of my mental health at the end of the day. There’s only so many days we can stay being couped inside the house. We are after all “SOCIAL ANIMALS”. It depends on the context too. They may be fortunate enough to enjoy their company of atleast their families however, me like many residents of KL are living by ourselves without our family. This was also a squabble I had with one of my colleagues who said the exact same thing as above. All I ask for people with such mindset is to be empathetic and listen to other people.

I solved a medium sql challenge and I am very happy about it.

Medium SQL Challenge:

WITH temp1 AS
(SELECT f.film_id, c.name
FROM category c JOIN film_category f
ON c.category_id = f.category_id
), temp2 AS
(SELECT i.film_id, i.inventory_id, r.rental_id, t.name
FROM inventory i JOIN rental r
ON i.inventory_id = r.inventory_id
JOIN temp1 t
ON i.film_id = t.film_id
ORDER BY film_id, inventory_id) 

SELECT film_id, name, count AS rentals, avg_rentals_category
FROM(
SELECT film_id, name, count, 
AVG(count) OVER (PARTITION BY name) AS avg_rentals_category
FROM(
SELECT film_id, name, COUNT(*)
FROM temp2
GROUP BY film_id, name
ORDER BY film_id) AS k
ORDER BY film_id) AS l
WHERE film_id <=10;