SQLPad Forum question: 168. Average number of streams in the US

168. Average number of streams in the US

Need your help to know why is my query answer not matching with the expected one.

with cte as
(SELECT customer_country, count(distinct v.stream_id) as counters,d.date from dates d left join video_stream v on d.date=v.stream_dt
group by d.date)

select date, counters as num_streams from cte
where date>='2021-08-01' and date<'2021-08-08' and customer_country like'US'

In my answer the rows with zero num_rentals are missing.

Answers

Comment

Hi Abbas,

The customer_country is causing the missing zeros, when you are doing the left join in CTE, the missing dates will have null customer_country, and your where clause in the last line removed those records.

To keep those records, you will need to handle the coutry in the LEFT JOIN part of your code.

This is a tricky question, and it boils down to the sequence of the exeuction by the database engine.

The where clause is applied after the join condition, and has to be taken care of.

Hope it helps,
Leon

Here is a modified version of your code which works.

with cte as
(SELECT  count(distinct v.stream_id) as counters,d.date 
     from dates d left join video_stream v 
     on d.date=v.stream_dt
     and customer_country = 'US'
group by customer_country, d.date)

select date, counters as num_streams from cte 
where date>='2021-08-01' and date<'2021-08-08'

Leon (949)

Oct. 4, 2023, 8:49 a.m.