skip to Main Content

I’m working with a Postgres table named orders that looks like this:

user_id   product   order_date
1     pants         7/1/2022
2     shirt         6/1/2022
1     socks         3/17/2023
3     pants         2/17/2023
4     shirt         3/13/2023
2     pants         8/15/2022
1     hat           4/15/2022
5     hat           3/14/2023
2     socks         12/3/2022
3     shirt         4/15/2023
4     socks         1/15/2023

Here is a dB Fiddle with the data:

https://www.db-fiddle.com/f/uNGjP7gpKwdPGrJ7XmT7k3/0

My goal is to output a table that shows the sequence of a customer’s orders. For example, customer 1 first purchased a hat, then purchased pants, and finally purchased socks.

Here is the desired output:

user_id   first_order   second_order    third_order
1     hat           pants           socks
2     shirt         pants           socks
3     pants         shirt           <null>
4     socks         shirt           <null>
5     hat           <null>          <null>

I used RANK() OVER (PARTITION BY user_id ORDER BY order_date) AS rank to arrange the sequence of the orders by user_id. This works in "long" form. However, I’m not able to translate this into the form of the table above.

How would I do that?

Thanks for your help,
-Rachel

2

Answers


  1. You can use a series of left joins on a subquery with the ranks. Your joining criteria can be to join the relation on both the user name and an incremented rank number. This way, rank 2 rows are joined onto rank 1 rows for a given user, and so on:

    with cte as (
       select o.*, rank() over (partition by user_id order by order_date) r from orders o
    )
    select c.user_id, c.product, c1.product, c2.product from cte c
    left join cte c1 on c1.user_id = c.user_id and c1.r = c.r + 1
    left join cte c2 on c2.user_id = c.user_id and c2.r = c1.r + 1
    where c.r = 1
    

    See fiddle.

    Login or Signup to reply.
  2. You can do it using the conditional aggregation, using group by, case clause and the aggregation function max() :

      SELECT
        *
        , RANK() OVER (PARTITION BY user_id ORDER BY order_date) AS rank
    FROM
        orders
        
    GROUP BY
        user_id
        , product
        , order_date
    )
    select user_id, max(case when rank = 1 then product end) as first_order,
                    max(case when rank = 2 then product end) as second_order,
                    max(case when rank = 3 then product end) as third_order
        
    from cte
    group by user_id
    

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search