SQL self-join compare current record with the record of the previous date

Question

From the attached transaction table, the cardOldValue of each transaction should equal to the CardNewValue of the last transaction.

I want to identify the TransactionID and the CardNumber of some suspicious cards who's CardOldValue is greater than the CardNewValue of the previous transaction.

In the example, the transactionID '003' and cardNumber '1234' should be captured as it's greater than the previous transaction cardNewValue '50'.

Following is what I have so far, but it doesn't give me the data I am after. Apparently I am not quite sure how self-join work:

SELECT 
    f1.TransactionID, f1.cardNumber, f1.cardOldValue, 
    f2.cardNewValue, f1.transactionDate, f2.transactionDate
FROM 
    Transaction f1, Transaction F2
WHERE 
    f2.transactionDate = (SELECT MAX(transactionDate) 
                          FROM Transaction
                          WHERE transactionDate < f1.transactionDate
                            AND cardNumber = f2.cardNumber
                            AND f1.cardOldValue > f2.cardNewValue)

I tested the sub-query separately, it gives me the date that is just older than the current date. So could you please let me know how to self-join the table properly?

Answer 1

-- Finding previous record without a LAG() function:
SELECT
    this.TransactionID, this.cardNumber , this.transactionDate
        , this.cardOldValue, this.cardNewValue 
    , prev.TransactionID, prev.transactionDate
        , prev.cardNewValue
FROM Transaction this
JOIN Transaction prev
        ON this.cardNumber = prev.cardNumber            -- same card
        AND prev.transactionDate < this.transactionDate -- older date
        AND NOT EXISTS ( SELECT *                       -- no dates in between
                FROM Transaction mid
                WHERE mid.cardNumber = this.cardNumber
                AND mid.transactionDate < this.transactionDate
                AND mid.transactionDate > prev.transactionDate
        )
WHERE this.cardOldValue > prev.cardNewValue -- suspect
        ;