365 Days of Daily Coding Challenge: Day 82

I am currently reading Viktor Frankl’s “Man Search For Meaning” and it has sparked a part inside me that I am unable to explain. It’s as though I have doused the little fire that had been building inside me. As I lay awake listening to my favorite singer IU, I am feeling immensely blessed and warmth radiating towards me.

Medium SQL Challenge:

SELECT * 
FROM
(SELECT *, ntile(100) OVER (PARTITION BY category 
ORDER BY revenue) AS percentile
FROM
(SELECT c.name AS category, f.film_id, SUM(p.amount) AS revenue
FROM film f JOIN film_category l
ON f.film_id = l.film_id
JOIN category c
ON l.category_id = c.category_id
JOIN inventory i
ON l.film_id = i.film_id
JOIN rental r
ON i.inventory_id = r.inventory_id
JOIN payment p
ON r.rental_id = p.rental_id
GROUP BY c.name, f.film_id) AS k) AS l
WHERE film_id <= 20;

Difficult SQL Challenge

SELECT customer_id, new - amount AS delta
FROM
(SELECT customer_id, payment_ts, amount,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY payment_ts) as num,
LAG(amount) OVER (PARTITION BY customer_id) AS new
FROM payment
WHERE customer_id in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)) AS k
WHERE num = 2;