CODEWITHSUNDEEP
sql-servert-sqllag
Jasper Schiks
Jasper Schiks

Reputation: 188

SQL LAG Days since last order

Hi I am trying to create a windowed query in SQL that shows me the days since last order for each customer.

enter image description here

It now shows me the days in between each order.

What do I need to change in my query to have it only show the days since the last and the previous order per customer? Now it shows it for every order the customer made.

Query:

SELECT klantnr,besteldatum,
      DATEDIFF(DAY,LAG(besteldatum) OVER(PARTITION BY klantnr ORDER BY besteldatum),besteldatum) AS DaysSinceLastOrder
FROM bestelling
GROUP BY klantnr,besteldatum;

Upvotes: 1

Views: 1832

Answers (1)

SqlZim
SqlZim

Reputation: 38063

You can use row_number() to order the rows by besteldatum for each klantnr, and return the latest two using a derived table (subquery) or common table expression.

derived table version:

select klantnr, besteldatum, DaysSinceLastOrder
  from (
    select klantnr, besteldatum
      , DaysSinceLastOrder = datediff(day,lag(besteldatum) over (partition by klantnr order by besteldatum),besteldatum) 
      , rn = row_number() over (partition by klantnr order by besteldatum desc)
    from bestelling
    group by klantnr, besteldatum
  ) t
where rn = 1

common table expression version:

;with cte as (
  select klantnr, besteldatum
    , DaysSinceLastOrder = datediff(day,lag(besteldatum) over (partition by klantnr order by besteldatum),besteldatum) 
    , rn = row_number() over (partition by klantnr order by besteldatum desc)
  from bestelling
  group by klantnr, besteldatum
)
select klantnr, besteldatum, DaysSinceLastOrder
from cte
where rn = 1

If you want one row per customer, rn = 1 is the proper filter. If you want n number of latest rows, use rn < n+1.

Upvotes: 2

Related Questions