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