Forum

Posted by Yang, Aug. 4, 2022, 7:21 a.m.

Question 73 Avg days a customer turns a happy customer

Could someone advise why my following query is not returning the correct result? My result is 72 instead of 71. 

--first cte returns row_number

WITH cte AS
(SELECT 
customer_id
,DATE(rental_ts)
,ROW_NUMBER() OVER (PARTITION BY customer_id ORDER by rental_ts) AS row_num
FROM rental)

--second cte returns records where a customer has 1st and/or 10th rental record 

,cte2 AS
(SELECT
customer_id
,date
FROM cte
WHERE row_num in (1, 10))

--third cte treats 1st rental date as previous, and 10th rental date as current, if no 10th rental date, it will be null

,cte3 AS
(SELECT
customer_id
,date as previous
,lead(date,1) OVER(PARTITION BY customer_id ORDER BY date) as current
FROM cte2)

--calculate avg difference between 10th rental date and 1st rental date, remove null record

SELECT
ROUND(AVG(current-previous))
FROM cte3
WHERE current IS NOT NULL

Answers

You likely have solved this by now, but I believe the issue is you're not using the hint given in the question for determining the number of days. After some fiddling, the totals I had were 599 customers and 42481 total days - this averages to 71 when rounded out. Yours was 599 customers and 42861 days. This is likely due to the conversion issues of casting to a date (ditching the time / timezone data) and working from there.

I think your query logic is interesting, you just need to modify the query a bit to maintain the data types and perform the extract example shown - I verified this by modifying your query and it returns the same as my solution.

SQLPad user avatar

Mike (228)

Aug. 24, 2022, 9:53 p.m.