Postgresql – How to force Redshift/Postgres to aggregate nth_value

postgresqlredshiftwindow functions

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?

Best Answer

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
Related Question