1

I'm trying to retrieve the 2nd, 3rd, 4th order/transaction from an orders table using a query that looks like this:

select 
    customer_email,
    nth_value(created_at, 1) over (partition by customer_email),
    nth_value(created_at, 2) over (partition by customer_email),
    nth_value(created_at, 3) over (partition by customer_email),
    nth_value(created_at, 4) over (partition by customer_email)
from 
    fact_orders
where
group by 
    customer_email,
    created_at
limit 100

My goal is to get the 1st, 2nd, 3rd, and 4th order for each customer. I'm forced to do a group by on customer_email and created_at, which results in a row for each order and date by a customer.

The nth_value yields the correct results, but I would prefer a result set that looks as so:

customer_email, first_order, second_order, third_order, fourth_order

With only 1 record per customer.

How can I achieve this?

  • Why are you forced to group by created_at? – Lennart Jul 10 '18 at 5:26
  • I think Redshift/Postgres strictly enforce it. – john Jul 10 '18 at 5:28
  • I've added an answer. I know nothing about Redshift, but if it hasn't deviated too far from postgreSQL it should work. – Lennart Jul 10 '18 at 5:35
  • In Postgres you could aggregate the values into a sorted array but that wouldn't be possible with Redshift. Do you need a Postgres or a Redshift solution? – a_horse_with_no_name Nov 8 '19 at 7:26
0

I imagine something like this should work:

select 
    customer_email,
    nth_value(created_at, 1) over (partition by customer_email
                                   order by created_at),
    nth_value(created_at, 2) over (partition by customer_email
                                   order by created_at),
    nth_value(created_at, 3) over (partition by customer_email
                                   order by created_at),
    nth_value(created_at, 4) over (partition by customer_email
                                   order by created_at)
from fact_orders 
where ...
limit 100

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.