SQLPad Forum question: # 14: Good and Bad days correct code but diff…

# 14: Good and Bad days correct code but different outcome

Hello,

Why won't this produce the expected outcome?
As per worded problem, it's flawless

with a as(SELECT * from rental
where extract(year from rental_ts) = 2020 and extract(month from rental_ts) = 5),

b as (select *, extract(day from rental_ts) as day_num
from a),

c as (select count(rental_id) as total_rentals, day_num
from b
group by day_num),

d as (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 c)

select * from d;

Answers

Comment

The rentals made from May 25, 2020, to May 31, 2020, mean that there were no records in the [rental] table for the preceding days.

We need to cover all the days of May 2020, requiring an expansion of the [rental] table. To do that, we apply a FULL OUTER JOIN on [ dates ] and [ rental ] based on the date (remember to cast dtype of [rental_ts])

HERE IS THE FULL CODE

WITH FULL_DATES AS

(

SELECT DATES.*

, RENTAL_ID

FROM DATES FULL OUTER JOIN RENTAL ON DATES.DATE = DATE(RENTAL_TS)

)

, DAILY_RENTALS AS

(

SELECT DATE

, COUNT(RENTAL_ID) AS NUM_RENTALS

FROM FULL_DATES

WHERE MONTH = 5 AND YEAR = 2020

GROUP BY DATE

)

SELECT SUM(CASE WHEN NUM_RENTALS > 100 THEN 1 ELSE 0 END) AS good_days

, SUM(CASE WHEN NUM_RENTALS <= 100 THEN 1 ELSE 0 END) AS bad_days

FROM DAILY_RENTALS

Võ Đức (2)

March 25, 2025, 6:38 a.m.