Get all IDs whose first transaction was a succesful card payment

I am trying to fetch all the USER_IDs whose 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 greatily 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


Read more here: https://stackoverflow.com/questions/64946580/get-all-ids-whose-first-transaction-was-a-succesful-card-payment

Content Attribution

This content was originally published by tacd at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: