SQL Calculate Average time between first and second order? (do sql calculations based on min and min+1?)

Question

I have a simple table that contains the customer email, their order count (so if this is their 1st order, 3rd, 5th, etc), the date that order was created, the value of that order, and the total order count for that customer.

Here is what my table looks like

Email          Order# Date     Value TotalOrders
r2n1w@gmail.com 1   12/1/2016   85  5
r2n1w@gmail.com 2   2/6/2017    125 5
r2n1w@gmail.com 3   2/17/2017   75  5
r2n1w@gmail.com 4   3/2/2017    65  5
r2n1w@gmail.com 5   3/20/2017   130 5
ation@gmail.com 1   2/12/2018   150 1
ylove@gmail.com 1   6/15/2018   36  3
ylove@gmail.com 2   7/16/2018   41  3
ylove@gmail.com 3   1/21/2019   140 3
keria@gmail.com 1   8/10/2018   54  2
keria@gmail.com 2   11/16/2018  65  2

First I calculated the average time between all orders taking the min order data and the max order date, and calculating the average between them using the amount of orders.

Here is how I did that (with the help of some SO users):

select email,
       datediff(day,  min(Date), max(Date)) / nullif(total-1, 0) as AvgDays
from Table
group by email;

This gives me the average in between purchase days per customer. Then I joined that table as a subquery to the original table and just joined it on customer email.

Now what I want is per customer, the days it took to get from the first order to the second (0 or null if there is no second order), and the days it took to get from the second order to the third (likewise if there is no third order)

So it would be pretty much exactly like my upper query, but instead of doing datediff for min and max, it would be datediff for min, and min plus 1. Or min plus 1 and min plus 2. And so on.

Is this possible?

Or would I be able to do this doing some case statements doing where Order# =2..

Also, I don't need this recursively if that is too hard, only the first three orders would be enough.

Answer 1

You can do this using conditional aggregation:

select email,
       datediff(day, min(Date), max(Date)) / nullif(total-1, 0) as AvgDays,
       datediff(day,
                 max(case when seqnum = 1 then date end),
                 max(case when seqnum = 2 then date end)
                ) as days_1_to_2,
       datediff(day,
                 max(case when seqnum = 2 then date end),
                 max(case when seqnum = 3 then date end)
                ) as days_2_to_3
from (select t.*,
             row_number() over (partition by email order by date) as seqnum
      from Table t
     ) t
group by email;