Forum

Posted by Andrei, April 6, 2022, 5:51 a.m.

#43 odd behaviour

Can someone explain why the query below does not work on #43 It's quite similar to the solution yet it does not work. Seems very odd.

select
  have_rented,
  count(*)
from (
  select
      case 
          when c.customer_id is not null then 'rented' else 'never-rented'
      end as have_rented
  from customer c
  left join (
    select 
      distinct customer_id
    from rental
    where extract(year from rental_ts)=2020
    and extract(month from rental_ts)=5
  ) r
  on r.customer_id=c.customer_id
) t
group by 1

Answers

Your code has a bug.

c.customer_id is always not null in the left join, you need to replace it with r.customer_id for your case when statement.

 

SQLPad user avatar

Leon (949)

April 6, 2022, 11:56 a.m.