Posted by Ayush, Nov. 21, 2023, 2:50 p.m.
An approach ! Can this be simplified?
WITH CTE AS (
SELECT d.date,
SUM(CASE WHEN r.rental_id IS NULL THEN 0 ELSE 1 END) AS booked_flag_sum
FROM dates AS d
LEFT JOIN rental AS r ON d.date = DATE(r.rental_ts)
WHERE EXTRACT(MONTH FROM d.date) = 5 AND EXTRACT(YEAR FROM d.date) = 2020
GROUP BY 1
),
CTE2 AS (
SELECT date,
CASE WHEN booked_flag_sum <= 100 THEN 0 ELSE 1 END as good_bad_flag
FROM CTE
)
SELECT
SUM(CASE WHEN good_bad_flag = 1 THEN 1 ELSE 0 END) as good_days,
SUM(CASE WHEN good_bad_flag = 0 THEN 1 ELSE 0 END) as bad_days
FROM CTE2;