SQLPad Forum question: Question 118 Fill in 0s for dates with no boo…

Question 118 Fill in 0s for dates with no booking

Hello, I understand the tricky part of this question is there are days with no bookings so 0s need to be filled in by having a full date table LEFT JOINED with the transactional table. However, I don't understand why my code won't work.

Could someone take a look?

SELECT
d.date
,CASE WHEN COUNT(DISTINCT booking_id) IS NULL THEN 0 ELSE COUNT(DISTINCT booking_id) END AS booking
FROM dates d
LEFT JOIN bookings b on d.date = b.date
LEFT JOIN listings l on b.listing_id = l.listing_id
WHERE
d.date BETWEEN '2021-07-01' AND '2021-07-31'
AND l.country = 'US'
GROUP BY 1

Answers

Comment

I think there are two primary issues with your query:

1) Consider what happens with your WHERE clause filtering on the country on a day when there are no bookings.

2) Remember that not all aggregate functions handle null values the same - Does COUNT ever return a null value? You may want to consider using a different aggregate function.

Mike (228)

Sept. 14, 2022, 5:49 p.m.