SQL面试题---查询平均交易间隔

给定transactions表，查询各用户平均交易间隔。

     `transactions`   
     +---------------------+---------+     
     | user_id             | int     |     
     | transaction_time    | datetime|       
     +---------------------+---------+

 

解题思路：1，将用户的每个交易时间与该用户的下一个交易时间放在同一行中。使用窗口函数LEAD达到此效果。

                  2，将各用户的下一个交易时间减去之前的交易时间，得到一个差值。使用DATEDIFF函数计算此差值。

                  3，计算各用户的平均交易时间的差值。

 

答案：

WITH next_trans AS (
    SELECT user_id, 
           transaction_time,
           LEAD(transaction_time, 1) OVER (PARTITION BY user_id ORDER BY transaction_time) AS next_time
    FROM transactions),

trans_diff AS (
    SELECT user_id.
           DATEDIFF(transaction_time, next_time) AS diff
    FROM next_trans);

SELECT user_id, AVG(diff) 
FROM trans_diff 
GROUP BY user_id;