SQLPad.io hard question 2

This is the question that we are going to answer in this post (you can find it here):

---

First of all, we need to take into account that the rental table does not have data for days that have no rentals. However, we still need to consider those days and count them as having 0 rentals. That can be done by starting from the date table (which contains every day for May 2020 without any gaps).

Since the date and rental tables are not directly related in the database schema, we need to create a custom join condition based on the date:

SELECT
EXTRACT(DAY from d.date) AS day_,
COUNT(r.rental_id) AS n_rentals
FROM dates d
LEFT JOIN rental r
ON DATE(r.rental_ts) = d.date
WHERE EXTRACT(MONTH from d.date) = 5
AND EXTRACT(YEAR from d.date) = 2020
GROUP BY 1

With this query, we start from the date table and then LEFT JOIN it to rental, which guarantees that every day in May 2020 will be included in the analysis. The count of rentals will be 0 for days that have no data in the rental table:

---

Now we can get to the requested result by creating custom columns with CASE and aggregating them:

WITH temp_ AS(
  SELECT
  EXTRACT(DAY from d.date) AS day_,
  COUNT(r.rental_id) AS n_rentals
  FROM dates d
  LEFT JOIN rental r
  ON DATE(r.rental_ts) = d.date
  WHERE EXTRACT(MONTH from d.date) = 5
  AND EXTRACT(YEAR from d.date) = 2020
  GROUP BY 1
  )
SELECT
	SUM(CASE WHEN n_rentals > 100 THEN 1 ELSE 0 END) AS good_days,
	SUM(CASE WHEN n_rentals <= 100 THEN 1 ELSE 0 END) AS bad_days
FROM temp_

For the good_days column, for example, PostgreSQL checks the temp_ CTE row by row and determines if the value for the n_rentals column is greater than 100. If it is, then the value for that row will be 1 (and 0 otherwise). After that, a simple SUM over our custom column will give us the number of good days in May 2020. We do the same thing for bad days and get to the final table: