Posted by Abhisek, Dec. 31, 2021, 1:19 a.m.

14. Good days and bad days

This query returns 1 for number of bad days. In the solution 1, you need to subtract from 31 to get bad days. Did anyone else run into this problem? A left join with the date table should give me null for the missing rental days but that does not happen.
WITH rental_agg AS (
SELECT DATE(rental_ts) AS day,
count(rental_id) as total_rentals
FROM rental
GROUP BY 1)
SELECT
SUM(CASE WHEN total_rentals >100 THEN 1 ELSE 0 END) AS good_days,
SUM(CASE WHEN total_rentals > 100 THEN 1 ELSE 0 END) AS bad_days
FROM dates d
LEFT JOIN rental_agg as ra on d.date = ra.day
WHERE d.date >= '2020-05-01' AND d.date <= '2020-05-31'
LIMIT 5;

Answer

Answers

Comment

Hey Abhisek,
There were some bugs in your code, but you are almost there.
I'd break down your query into multiple steps and take a look at the intermediary results.
Cheers and happy new year!
Leon

Leon (949)

Jan. 2, 2022, 10:50 a.m.

Comment

select
sum(case when CntRentals > 100 then 1 else 0 end) as good_days,
sum(case when CntRentals <=100 then 1 else 0 end) as bad_days
from
(
select
dt.date,
case when rental_dt is null then 0 else CntRentals end as CntRentals
from
dates as dt
left join (
SELECT
cast(rental_ts as date) as rental_dt,
count(distinct rental_id) as CntRentals
FROM rental
group by 1
) as re on dt.date = re.rental_dt
where
dt.year = 2020
and dt.month = 5
)x;

Ashish (2)

May 14, 2022, 12:53 p.m.