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