Question 108582 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL: how to rank events within a cohort by time

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

Tags:

2

Answers


  1. 0

    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. 0

    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