0

In this question there are several ways to visit previous row in a SELECT statement. However, I cannot figure out how to do it conditionally.

For example, suppose we have a Transactions table:

customer_id  purchase_date  merchandise_type  merchandise_name
-----------------------------------------------------------------
1            12 Apr         Rice              Jasmine
1            18 Apr         Rice              Basmati 
1            19 Apr         Rice              Long-Grain
3            13 Apr         Rice              Jasmine

I'd like to find out how long a customer changed his/her mind after buying an item, expected output is:

customer_id  merchandise_name  days
------------------------------------
1            Jasmine           6
1            Basmati           1

Customer 1 bought Jasmine rice then bought Basmati rice 6 days later, so "days" in the first record is 6. Following code is able to do this:

select customer_id, merchandise_name,
       purchase_date - LAG(purchase_date) over (order by purchase_date) as days
from Transactions

However, it won't work when there are other types of merchandise:

customer_id  purchase_date  merchandise_type  merchandise_name
-----------------------------------------------------------------
1            12 Apr         Rice              Jasmine
1            13 Apr         Cafe              Maxwell
1            18 Apr         Rice              Basmati 
1            19 Apr         Rice              Long-grain
1            19 Apr         Cafe              Nescafe
3            13 Apr         Rice              Jasmine
3            14 Apr         Cafe              Nescafe

Is it possible to get a previous row with some condition? something like:

...
order by purchase_date
where LAG(merchandise_type) = merchandise_type
Community
  • 1
  • 1
Deqing
  • 14,098
  • 15
  • 84
  • 131

1 Answers1

2

What you are looking for is the PARTITION BY clause in your OVER function:

select customer_id, merchandise_name,
       purchase_date - 
         LAG(purchase_date) over (partition by customer_id, merchandise_type
                                  order by purchase_date) as days
from Transactions

Without this clause you will get any previous value for the purchase_date.

cha
  • 10,301
  • 1
  • 18
  • 26
  • Why wrong? What if there are different product types with the same name? Say hypothetically there is a Rice Jasmine and Tea Jasmine? – cha Apr 26 '17 at 05:49
  • Yes, I have re-read the question and found that the OP actually wants to know by the type only, regardless of name – cha Apr 26 '17 at 05:52