Calculate median date difference for ID

Question

i have table with transactions data. I have to calculate median difference between transactions in days for every client. In table are 3 important columns:

**Transaction_id | client_id| transaction_date**

Transaction_id is primary. Have u any idea how i can handle it?

EDIT: One important info: I don't know how many transactions customers have.

Does this answer your question? [Function to Calculate Median in SQL Server](https://stackoverflow.com/q/1342898/2029983) — Larnu, Aug 24 '20 at 11:42

score 0 · Accepted Answer · answered Aug 24 '20 at 11:44

Hmmm . . . I think you want percentile_cont(). That would be:

select distinct client_id,
       percentile_cont(0.5) within group (order by day_diff) over (partition by client_id) as median_day_diff
from (select t.*,
             datediff(day,
                      lag(transaction_date) over (partition by client_id order by transaction_id,
                      transaction_date
                     ) as day_diff
      from t
     ) t

Calculate median date difference for ID

1 Answers1