Average days duration between multiple transactions and latest transaction gap mysql-CodePudding

I have the transaction table with the following columns :

TRANSACTION_ID, USER_ID, MERCHANT_NAME, TRANSACTION_DATE, AMOUNT

-)Query to calculate time difference (in days) between current and previous order of each customer -)the avg time difference between two orders for every customer.

Note : Exclude users with single transactions

I tried the following code to get the 1st part of the query but it looks too messy

with t1 as
(Select USER_ID,TRANSACTION_DATE,Dense_rank() over(partition by USER_ID order by TRANSACTION_DATE desc) as r1
from CDM_Bill_Details
order by USER_ID, TRANSACTION_DATE desc)

Select t11.USER_ID, datediff(t11.TRANSACTION_DATE,t111.TRANSACTION_DATE) from t1 as t11,t1 as t111
where (t11.r1=1 and t111.r1=2) and (t11.USER_ID=t111.USER_ID)

CodePudding user response：

Please try this:

with t2 as (select *,
lag(t1.TRANSACTION_DATE, 1) OVER (PARTITION BY USER_ID ORDER BY TRANSACTION_DATE) AS previous_date,
datediff(t1.TRANSACTION_DATE, lag(t1.TRANSACTION_DATE, 1) OVER (PARTITION BY USER_ID ORDER BY TRANSACTION_DATE)) AS diff_prev_curr  
from CDM_Bill_Details t1)

select *,
avg(diff_prev_curr) OVER (PARTITION BY USER_ID) AS avg_days_diff
from t2
where previous_date is not null