Get all IDs where the first transaction was a succesful card payment

Question

I am trying to fetch all the USER_IDs where the first transaction was a successful card payment

However, I am getting dubious results - I am not necessarily fetching the first transaction that is a card payment. For some users it is indeed correct. For others, however, I am currently fetching another transaction.

Any guidance on what might be wrong would be greatly appreciated

SELECT
    t."USER_ID",
    (t."AMOUNT") AS amount
FROM
    transactions t
INNER JOIN 
    (SELECT MIN( tt."CREATED_DATE" ) AS date, tt."USER_ID"
     FROM transactions tt
     WHERE      
        tt."STATE" = 'COMPLETED'
        AND tt."TYPE" = 'CARD_PAYMENT'
     GROUP BY tt."USER_ID") tt ON t."USER_ID" = tt."USER_ID"
ORDER BY
    amount DESC

Answer 1

One method uses aggregation, to compare the earliest date for a user with the earliest date for a successfully credit card transaction:

select user_id
from transactions t
group by user_id
having min(created_date) = 
       min(case when state = 'COMPLETED' and type = 'CARD_PAYMENT' then created_date end);

Another method -- if you want the entire row -- is not exists:

select t.*
from transactions t
where t.state = 'COMPLETED' and
      t.type = 'CARD_PAYMENT' and
      t.created_date = (select min(t2.created_date)
                        from transactions t t2
                        where t2.user_id = t.user_id 
                       );

Or, use window functions:

select t.*
from (select t.*,
             row_number() over (partition by user_id order by created_date) as seqnum
      from t
     ) t
where seqnum = 1 and
      t.state = 'COMPLETED' and
      t.type = 'CARD_PAYMENT' ;