Adding a currency conversion to a SQL query

Question

I have a database with a list of user purchases.

I'm trying to extract a list of users whose last successful purchase had a value of £100 or greater, which I have done:

SELECT 
    t.purchase_id
    t.user_id, 
    t.purchase_date, 
    t.amount, 
    t.currency,
FROM 
    transactions t
INNER JOIN
    (SELECT user_id, MAX(purchase_date) AS first_transaction
     FROM transactions
     GROUP BY user_id) frst ON t.user_id = frst.user_id 
                            AND t.created_date = frst.first_transaction
WHERE 
    amount >= 100
ORDER BY 
    user_id;

The problem is that some of my purchases are in USD and some are in CAD. I would like to ensure that the value of the latest purchase is over £100 GBP despite the purchase currency.

Luckily I have another table with exchange rates:

base_currency     currency       exchange_rate 
-----------------------------------------------
GBP               USD            1.220185624
GBP               CAD            1.602048721

So technically I just need to convert the amount using the exchange rate. I've hit a roadblock on how I can incorporate that into my current query. I'm thinking I need to create an extra column for amount_in_gbp but am not sure how to incorporate the case logic into my query?

Answer 1

You can avoid any JOIN statement:

SELECT t.purchase_id 
    ,t.user_id
    ,t.purchase_date
    ,t.amount
    ,t.currency
FROM transactions t
INNER JOIN (
    SELECT user_id
        ,MAX(purchase_date) AS first_transaction
    FROM transactions
    GROUP BY user_id
    ) frst ON t.user_id = frst.user_id
    AND t.created_date = frst.first_transaction
WHERE (
        SELECT t.amount / e.exchange_rate
        FROM exchange AS e
        WHERE t.currency = e.currency
        ) >= 100
ORDER BY user_id;

So that your column will be converted in GBP currency.