365 Days of Daily Coding Challenge: Day 84

Today, I did an activity that was beyond my comfort zone. I am very happy with that. I hope I continue to step out of my comfort zone so that I continue to grow.

I solved two medium sql challenge and managed to retain my position in the leadership board. I am currently 9th rank wise.

Medium SQL Challenge:

SELECT ROUND(AVG(EXTRACT(DAYS FROM tenth_rental_ts - first_rental_ts)))
FROM(
SELECT * , LAG(tenth_rental_ts) OVER(PARTITION BY customer_id 
							   ORDER BY tenth_rental_ts) AS first_rental_ts
FROM(
SELECT customer_id, rental_ts AS tenth_rental_ts, 
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY rental_ts) AS rown
FROM rental) AS k
WHERE rown IN (1,10)) AS l
WHERE tenth_rental_ts IS NOT null;

Medium SQL Challenge:

WITH temp AS
(
SELECT DISTINCT f.category_id, a.actor_id, 
COUNT(a.film_id) OVER (PARTITION BY category_id, actor_id) AS num_movies
FROM film_category f JOIN film_actor a
ON f.film_id = a.film_id
ORDER BY category_id, actor_id)

SELECT category_id, actor_id, num_movies
FROM(
SELECT *, MAX(num_movies) OVER (PARTITION BY category_id) AS max
FROM temp) AS k
WHERE num_movies = max;