Question 43

Very good question Tobias!

The tricky part is how the DB engine executes your query (in what order).

In your query:

The where date ... clause ran first, then the case when is applied on the filtered results.

To fix your query:

You need to run the case when count... before the where clause is executed.

A simple trick to do that is to move the where condition to be part of the join condition (join always run before the where clause).

Here is an updated query (passed the online judge):

with cr as 
(
  SELECT c.customer_id
      , case when count(r.rental_id) is Null then 0 else count(r.rental_id) end as count 
  FROM customer as c 
  left join rental as r on r.customer_id=c.customer_id
  and date(r.rental_ts)<= '2020-05-31' and date(r.rental_ts)>= '2020-05-01'
  group by c.customer_id

)
select case when count > 0 then 'rented' else 'never-rented' end as hass_rented
    , count(*) as count
From cr
group by hass_rented

hope it helps
--leon