365 Days of Daily Challenge: Day 78

My first project failed today. It was disheartening considering I had put effort for its success. However, I believe that there’s going to be many such failures along the way. And one day, I will finally see the daylight. What’s more they keep me grounded and stay humble.

I solved two medium challenge today in less than 15 minutes. Can you belive it? I would have taken almost 2 hrs to solve one challenge scouring the internet to find a clue. Lol!

Medium Challenge 1

SELECT * FROM (
SELECT f.film_id, f.title, f.length, g.name AS category, 
ROW_NUMBER() OVER (PARTITION BY g.name ORDER BY f.length) AS row_num
FROM film f JOIN film_category c
ON f.film_id = c.film_id
JOIN category g
ON g.category_id = c.category_id) AS k
WHERE row_num = 1;

Medium Challenge 2

SELECT * 
FROM(
SELECT *, 
DENSE_RANK() OVER (PARTITION BY store_id ORDER BY revenue DESC) AS ranking
FROM(
SELECT c.store_id, c.customer_id, SUM(p.amount) AS revenue
FROM customer c JOIN payment p
ON c.customer_id = p.customer_id
GROUP BY c.store_id, c.customer_id) AS k) AS l
WHERE ranking < 6;