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?

Join on the currency conversion table and do the math... – devlin carnate Jul 31 '19 at 20:42 — devlin carnate, Jul 31 '19 at 20:42

score 1 · Answer 1 · answered Jul 31 '19 at 20:43

You join to the table:

SELECT t.*,
       (t.amount / exchange_rate) as amoung_gbp
FROM transactions t LEFT JOIN
     exchange e
     ON t.currency = e.currency AND e.base_currency = 'GBP'

If you want to put this in a where clause, you need to repeat the expression:

where (t.amount / exchange_rate) > 100

Francisco Sevilla · Accepted Answer · 2019-07-31T21:41:17.857

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.

edited Jul 31 '19 at 21:41

answered Jul 31 '19 at 20:55

Francisco Sevilla

94
1
7

1

This seems like a perfectly elegant solution... except for some reason I'm getting an 'amount_GBP column does not exist' error? – DDiran Jul 31 '19 at 21:10
1

Unfortunately an aliased column can't be used in `WHERE` clause: [link](https://stackoverflow.com/a/16162147/5533960), you can move the entire Subquery to that. – Francisco Sevilla Jul 31 '19 at 21:22

Adding a currency conversion to a SQL query

2 Answers2