Posted by Martin, July 3, 2023, 2:01 p.m.

q69

Hello.

I am curious regarding question 69, I have below code, which gives me an anser of 146, I cannot understand why it is not giving me the correct answer. Is it a missuse of lag-date or the lack of the min function?

Please have a look if possible:

with cte_ques_1 as (

select date(rental_ts), case when date_minus_lag<1 then 1 else 1 end as date_minus_lead_corr

from (

select *,
date(rental_ts),
lag(date(rental_ts),1) over(partition by customer_id) as lag1,

date(rental_ts)-lag(date(rental_ts),1) over(partition by customer_id) as date_minus_lag
from rental

where date(rental_ts)>='2020-05-24' and date(rental_ts)<='2020-05-31'

order by customer_id, rental_ts) x

where date_minus_lag=1 OR date_minus_lag=-1 )

select count(*)

from cte_ques_1

;

This question is closed

Answers

Comment

Hey Martin,

Good work, you are definitely getting there.

I'd suggest print out the result of the customers from your CTE and compare it with our solution, find missing customers and focus on a handful of them.

For example: when I print out your CTE, customer_id = 7 is missing, then I'd recreate customer 7's rental history, compare it your solution with our official side by side, step by step, and see which step was causing the problem.

For example, here is customer_id = 7's rental history

SELECT customer_id, rental_ts
FROM exercise.rental
WHERE DATE(rental_ts) >= '2020-05-24'
AND DATE(rental_ts) <= '2020-05-31'
AND customer_id = 7


7,2020-05-25 13:04:08.000000 +00:00
7,2020-05-26 02:30:46.000000 +00:00
7,2020-05-29 16:27:00.000000 +00:00
7,2020-05-31 04:07:15.000000 +00:00
7,2020-05-31 15:44:29.000000 +00:00

And you can see that he/she should be qualified, however, I don't see this person in your CTE, why?

Hope it helps,

Leon

Leon (949)

July 3, 2023, 3:24 p.m.

Comment

Hello again thanks for replying.

Didnt fully find the solution but suspecting it was:

"lag(date(rental_ts),1) over(partition by customer_id) as lag1,

date(rental_ts)-lag(date(rental_ts),1) over(partition by customer_id) as date_minus_lag"

that was supposed to be:

"lag(date(rental_ts),1) over(partition by customer_id order by customer_id, date(rental_ts)) as lag1,
date(rental_ts)-lag(date(rental_ts),1) over(partition by customer_id order by customer_id, date(rental_ts)) as date_minus_lag"

I solved it with a different query however, please see below:

"select count(distinct customer_id)

from (

select *,

date(rental_ts),

lag(date(rental_ts),1) over(partition by customer_id order by customer_id, date(rental_ts)) as lag1,

date(rental_ts)-lag(date(rental_ts),1) over(partition by customer_id order by customer_id, date(rental_ts)) as date_minus_lag

from rental

where date(rental_ts)>='2020-05-24' and date(rental_ts)<='2020-05-31'

order by customer_id, date(rental_ts) ) x

where date_minus_lag=1 "

Looks good Martin, glad you fix your own code, way to go!

Leon, July 5, 2023, 4:02 p.m.

Martin (9)

July 4, 2023, 11:32 a.m.