0

Say for example I have query like so:

select 
   t1.customer_id,
   nth_value(t2.created_at, 2) over(partition by t2.customer_id order by t2.created_at asc rows unbounded preceding and unbounded following) as second_purchase_date 
from 
   order_line_items t1
inner join 
   orders t2 
   on t1.order_id=t2.order_id

In the query above, I would get results like:

john@acme.co,2018-01-01
john@acme.co,2018-01-01
john@acme.co,2018-01-01

Because I am including line items (I need to do some additional things using line items).

Line items = the items that they ordered as related to a specific order (e.g., you bought in order #1: shoes, belt, hat)

I'm trying to retrieve their overall second order date. However, nth_value sees 3 rows, and believes that the 2nd purchase date is 2018-01-01, when realistically, this is all apart of the same order. This particular john@acme.co has never purchased a 2nd time so the result should be NULL.

Any ideas on how I can overcome this? (I have to use the order line items table for other reasons)

  • You may write a subquery that retrieves (customer_id, 2th purchase date) for all customers and join that on customer_id to your other tables. – Daniel Vérité Apr 27 at 12:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.