Say for example that I have a query like this:
to select t1.customer_id, nth_value (t2.created_at, 2) over (partition by t2.customer_id order by t2.created_at asc unbounded lines previous and next unbound) as second_purchase_date of order_line_items t1 internal join t2 orders on t1.order_id = t2.order_id
In the above query, I would get results such as:
john @ acme.co, 2018-01-01 john @ acme.co, 2018-01-01 john @ acme.co, 2018-01-01
Because I included line items (I have to perform additional operations with the help of line items).
Line items = items related to a specific order (for example, you bought in order # 1: shoes, belt, hat)
I'm trying to recover their second global order date.
However, nth_value sees 3 lines and thinks the second date of purchase is 2018-01-01. When this is realistic, it is the same order. This email@example.com never bought a second time, the result should be NULL.
Any ideas on how I can overcome this? (I have to use the table of command line items for other reasons)