365 Days of Daily Challenge: Day 89

I found this very cool web application to take notes. It’s called “Notion”. You can use it to replace OneNote. Some of the benefits over OneNote is that you can create several cool objects in the page like list, boards, timelines and so much more as well as several sub-pages under the parent page. This can also be used to replace Confluence and Jira offered by Atlassian. It’s quite a hip product. I have so much more to explore on this new web application.

I solved two easy sql challenges including the challenge I was unable to solve yesterday and one medium sql challenge. I have managed to finish all the free challenges in sqlpad except one difficult challenge.

Easy SQL Challenge 1:

SELECT * FROM 
(SELECT *, NTILE(4) OVER (ORDER BY num_rentals ASC)
FROM(
SELECT f.film_id, COUNT(r.rental_id) AS num_rentals
FROM film f JOIN inventory i
ON f.film_id = i.film_id
JOIN rental r
ON i.inventory_id = r.inventory_id
GROUP BY f.film_id
ORDER BY num_rentals ASC) AS k) AS l
WHERE film_id IN (1,10,11,20,21,30);

Easy SQL Challenge 2:

WITH movie_rev_by_cat AS (
    SELECT 
       F.film_id,
       MAX(FC.category_id) AS category_id,
       SUM(P.amount) AS revenue
    FROM film F
    INNER JOIN inventory I
    ON I.film_id = F.film_id
    INNER JOIN rental R
    ON R.inventory_id = I.inventory_id
    INNER JOIN payment P
    ON P.rental_id = R.rental_id
    INNER JOIN film_category FC
    ON FC.film_id = F.film_id
    GROUP BY F.film_id
)
SELECT film_id, perc_by_cat 
FROM (
SELECT *, 
  NTILE(100) OVER (PARTITION BY category_id 
				   ORDER BY revenue ASC) AS perc_by_cat
FROM movie_rev_by_cat) AS k
WHERE film_id IN (1,2,3,4,5);

Medium SQL Challenge:

WITH cust_rentals AS (
    SELECT C.customer_id, 
    MAX(C.store_id) AS store_id, -- one customer can only belong to one store
    COUNT(*) AS num_rentals FROM 
    rental R
    INNER JOIN customer C
    ON C.customer_id = R.customer_id
    GROUP BY C.customer_id
)
SELECT customer_id, store_id, quartile
FROM (
SELECT *, NTILE(4) OVER (ORDER BY num_rentals) AS quartile
FROM cust_rentals) AS k
WHERE customer_id IN (1,2,3,4,5,6,7,8,9,10)