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:
email@example.com,2018-01-01 firstname.lastname@example.org,2018-01-01 email@example.com,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 firstname.lastname@example.org 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)