SQL partition achieving "second_value" etc

Question

I'm calculating customers retention and want to segment my customers according to behavior in first, second, third and so-on purchases.

For example:

using first_value(had_coupon) over (partition by customer_id order by order_date DESC) 

I can segment by using a coupon in the first purchase effect retention.

I'm trying to figure out to do the same thing for the second and third purchase. Using the CASE statement I can give another value to customers who did not buy two, three or more times.

I've been using this site for help.

Answer 1

Use row_number() for marking first, second, third, etc + group by aggregation using max() or min() to group rows by customer/etc:

select max(case when rn=1 then had_coupon end) first_order_had_coupon,
       max(case when rn=2 then had_coupon end) second_order_had_coupon, 
       -- and so on
       --other group columns
from  
(
select had_coupon,
       --other columns
      row_number() over (partition by customer_id order by order_date DESC) rn
  from table
)s
group by group columns