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;

Answer

Answers


Comment
SQLPad user: Leon Wei

- Leon (934)

Nov. 23, 2023, 2:51 p.m.