Difference between multiple dates

Question

I am working in a database with multiple orders of multiple suppliers. Now I would like to know the difference in days between order 1 and order 2, order 2 and order 3, order 3 and order 4 and so on.. For each supplier on its own. I need this to generate the Standard Deviation for each supplier based on their days between orders.

Hopefully someone can help..

Answer 1

What you describe is lag() with aggregation:

select supplier,
       stddev(orderdate - prev_orderdate) as std_orderdate
from (select t.*,
             lag(orderdate) over (partition by supplier order by orderdate) as prev_orderdate
      from t
     ) t
group by supplier;