365 Days of Daily Coding Challenge: Day 86

Today I made a significant improvement towards my project. I am quite happy with it. I have yet to make progress on a data visualisation project that I announced couple of days ago. Seems like it is around the right time to start working on it. I will keep you all updated on the progress. First, I will need to select a data set that I am interested in. I will then create a blueprint of the insights that I want to draw out from the dataset.

Medium SQL Challenge:

WITH cust_revenue_by_cat AS (
    SELECT 
        P.customer_id,
	FC.category_id,
	SUM(P.amount) AS revenue
    FROM payment P
    INNER JOIN rental R
    ON R.rental_id = P.rental_id
    INNER JOIN inventory I
    ON I.inventory_id = R.inventory_id
    INNER JOIN film F
    ON F.film_id = I.film_id
    INNER JOIN film_category FC
    ON FC.film_id = F.film_id
    GROUP BY P.customer_id, FC.category_id
)
SELECT category_id, customer_id
FROM(
SELECT *, DENSE_RANK()OVER (PARTITION 
BY category_id ORDER BY revenue DESC) AS rnk
FROM cust_revenue_by_cat
) AS k
WHERE rnk = 1;

Difficult SQL Challenge

WITH store_daily_rev AS (
  SELECT 
    I.store_id, 
    DATE(P.payment_ts) date,
    SUM(amount) AS daily_rev
  FROM 
    payment P
  INNER JOIN rental R
  ON R.rental_id = P.rental_id
  INNER JOIN inventory I
  ON I.inventory_id = R.inventory_id
  WHERE DATE(P.payment_ts) >= '2020-05-01'
  AND DATE(P.payment_ts) <= '2020-05-31'
  GROUP BY I.store_id, DATE(P.payment_ts))
  
SELECT store_id, date, ROUND((daily_rev/lag-1) * 100) AS dod_growth
FROM(
SELECT store_id, date, daily_rev,
LAG(daily_rev) OVER (PARTITION BY store_id ORDER BY date)
FROM store_daily_rev
) AS k;