Postgresql order by created date and group by transaction id

user_701571 0
Viewed 23

My table format is like this

id transaction_id status created_at updated_at
uuid-1 a293b1fe0369e0198df3293a8aef9c97ea532b30 completed 2022-08-25 02:32:44 2022-08-25 02:32:44
uuid-2 a293b1fe0369e0198df3293a8aef9c97ea532b24 failed 2022-08-24 12:33:22 2022-08-24 12:33:22
uuid-3 3b97c805fc7ce00119433c5284102b47781f9f66 pending 2022-08-24 12:30:22 2022-08-24 12:33:22
uuid-4 a293b1fe0369e0198df3293a8aef9c97ea532b30 failed 2022-08-23 9:32:14 2022-08-23 9:32:14
uuid-5 a293b1fe0369e0198df3293a8aef9c97ea532b30 failed 2022-08-05 9:22:34 2022-08-05 9:22:34
uuid-6 a293b1fe0369e0198df3293a8aef9c97ea532b24 pending 2022-08-04 03:33:12 2022-08-04 03:33:12
uuid-7 a293b1fe0369e0198df3293a8aef9c97ea532b30 failed 2022-08-01 4:04:25 2022-08-01 4:04:25
uuid-8 a293b1fe0369e0198df3293a8aef9c97ea532b30 pending 2022-07-20 7:43:22 2022-07-20 7:43:22

I am trying to get results in this order

  • Latest user submitted transaction on top
  • Then order the actions that happened on each transaction in their created order

actions order will be pending, failed, completed.

transaction_id status created_at updated_at
3b97c805fc7ce00119433c5284102b47781f9f66 pending 2022-08-24 12:30:22 2022-08-24 12:33:22
a293b1fe0369e0198df3293a8aef9c97ea532b24 pending 2022-08-04 03:33:12 2022-08-04 03:33:12
a293b1fe0369e0198df3293a8aef9c97ea532b24 failed 2022-08-24 12:33:22 2022-08-24 12:33:22
a293b1fe0369e0198df3293a8aef9c97ea532b30 pending 2022-07-20 7:43:22 2022-07-20 7:43:22
a293b1fe0369e0198df3293a8aef9c97ea532b30 failed 2022-08-01 4:04:25 2022-08-01 4:04:25
a293b1fe0369e0198df3293a8aef9c97ea532b30 failed 2022-08-05 9:22:34 2022-08-05 9:22:34
a293b1fe0369e0198df3293a8aef9c97ea532b30 failed 2022-08-23 9:32:14 2022-08-23 9:32:14
a293b1fe0369e0198df3293a8aef9c97ea532b30 completed 2022-08-25 02:32:44 2022-08-25 02:32:44

I tried to group and order with RANK function like below, but no idea how to sort group by their first created date

select
    transaction_id,
    created_at,
    status,
    RANK() over (partition by transaction_id order by created_at) group_rank
from
    transactions;
group-by date postgresql
1 Answers

I would use ROW_NUMBER() and MAX() here:

WITH cte AS (
    SELECT *, MAX(created_at) OVER (PARTITION BY transaction_id) AS max_created_at,
              ROW_NUMBER() OVER (PARTITION BY transaction_id
                                 ORDER BY CASE status WHEN 'pending'   THEN 1
                                                      WHEN 'failed'    THEN 2
                                                      WHEN 'completed' THEN 3 END,
                                          created_at) rn
    FROM transactions
)

SELECT transaction_id, status, created_at, updated_at
FROM cte
ORDER BY max_created_at DESC, rn;

The ordering logic is that we first order each block of records belonging to the same transaction using the most recent created timestamp. Next, within each block we order by status. Finally, for two or more records having the same status, we again break that tie using the created timestamp.

user_18632290
Related