Search code examples
sqlpostgresqlgreatest-n-per-group

Display a table that will store for each user his first "success" date

This is my simple sql request:

select user_id, id_transaction, status_name, transaction_datetime 
from pay 
where status_name = 'success' 
  and id_transaction is not null 
order by user_id, transaction_datetime

And i see this

enter image description here

How can I display only one, first successful entry per user?

Like this

enter image description here

Solution

  • You can use DISTINCT ON as suggested in the comments but here is another solution using a CTE that may be more performant. I did not do anything to prove performance.

    WITH first_success AS
(
SELECT user_id
     , status_name
     , MIN(transaction_datetime) min_ts
  FROM t
 WHERE status_name = 'success'
   AND id_transaction IS NOT NULL
 GROUP BY userid, status_name
)
SELECT t2.user_id
     , t2.id_transaction
     , t2.status_name
     , t2.transaction_datetime
  FROM first_success fs
  JOIN t t2 ON  fs.min_ts = t2.transaction_datetime
            AND fs.user_id = t2.user_id
;

    Here is a dbfiddle to show both examples in action.