CODEWITHSUNDEEP
sqlsql-server
Jake Wagner
Jake Wagner

Reputation: 826

Joining on the same key on the next row

Suppose we have a table which contains customer_id, order_date, and ship_date. A reorder of the product occurs when the same customer's next order_date is within 30 days of the last ship_date.

select * from mytable

customer_id       order_date      ship_date
   1              2017-08-04      2017-08-09
   1              2017-09-01      2017-09-05
   2              2017-02-02      2017-03-01
   2              2017-04-05      2017-04-09
   2              2017-04-15      2017-04-19       
   3              2018-02-02      2018-03-01

Requested: Reorders

customer_id       order_date      ship_date
   1              2017-09-01      2017-09-05
   2              2017-04-15      2017-04-19

How can I retrieve only the records for the same customers who had reorders, next order_date within 30 days of the last ship_date.

Upvotes: 0

Views: 45

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270773

One method is lead():

select t.customer_id, t.order_date, t.next_ship_date
from (select t.*,
             lead(order_date) over (partition by customer_id order by order_date) as next_order_date
             lead(ship_date) over (partition by customer_id order by order_date) as next_ship_date
     from t
    ) t
where next_order_date < dateadd(day, 30, ship_date);

EDIT:

If you want the "reorder" row, just use lag():

select t.*
from (select t.*,
             lag(ship_date) over (partition by customer_id order by order_date) as prev_ship_date
     from t
    ) t
where prev_ship_date > dateadd(day, 30, order_date);

Upvotes: 1

Popeye
Popeye

Reputation: 35920

You can use exists as follows:

Select * from your_table t
Where exists (select 1 from your_table tt
Where tt.customer_id = t.customer_id
  And t.ship_date > tt.ship_date
  and t.ship_date <= dateadd(day, 30, tt.ship_date))

Upvotes: 1

Related Questions