365 Days of Daily Coding: Day 38

Today was a lazy day. I watched few episodes of a K-drama in Netflix, got bored and then continued the challenge for the day. Since I was feeling extra today, I solved 4 challenges: 2 easy, 1 medium and 1 difficult. The last challenge was the same challenge that I had tried few days earlier and was unable to solve. I had been thinking about the solution for a while and today, I was finally able to crack the challenge.

Easy Challenge 1

SELECT AVG(amount) AS avg
FROM payment
WHERE EXTRACT(YEAR FROM payment_ts) = 2020
AND EXTRACT(MONTH FROM payment_ts) = 05;

Easy Challenge 2

SELECT SUM(AMOUNT)/COUNT(DISTINCT customer_id) AS avg
FROM payment
WHERE EXTRACT(YEAR FROM payment_ts) = 2020 AND
EXTRACT(MONTH FROM payment_ts) = 02;

Medium Challenge

SELECT title FROM(
SELECT f.title, COUNT(a.actor_id) AS counts 
FROM film f LEFT JOIN film_actor a
ON f.film_id = a.film_id
GROUP BY f.film_id) AS k
WHERE counts >= 10
ORDER BY title ASC;

Difficult Challenge

WITH temp AS
  (SELECT DATE(rental_ts) AS dates,COUNT(rental_id) AS counts
	FROM rental
	WHERE EXTRACT (YEAR FROM rental_ts) = 2020 AND 
	EXTRACT (MONTH FROM rental_ts) = 05
	GROUP BY DATE(rental_ts),EXTRACT (DAY FROM rental_ts)
   ), 
 datej AS
   ( SELECT *
	FROM dates
	WHERE EXTRACT (YEAR from date) = 2020 AND
	EXTRACT(MONTH from date) = 05), 
 fulljoin AS
   (SELECT * 
       FROM temp t RIGHT JOIN datej d 
       ON t.dates = d.date),
 fulltable AS
   (SELECT *, 
     CASE
	WHEN counts > 100
	THEN 'good_day'
	ELSE 'bad_day'
    END cat FROM fulljoin),
 cats AS
   (SELECT year, cat, COUNT(*) 
     FROM fulltable
     GROUP BY cat, year),
goodtable AS 
  (SELECT year, count AS good_days from cats
	WHERE cat = 'good_day'), 
badtable AS 
 (SELECT year, count AS bad_days from cats
       WHERE cat = 'bad_day')

SELECT g.good_days, b.bad_days
FROM goodtable g LEFT JOIN badtable b
ON g.year = b.year;

• Facebook
• Instagram
• LinkedIn
• GitHub