MySQL – Sum revenue while correcting by daily exchange rates

I have two tables

Orders table

id       | total_price (EUR)| order_date |
---------|------------------|------------|
1        | 400              | 2021-05-01 |
2        | 1350             | 2021-10-04 |
3        | 760              | 2021-10-05 |

Exchange rates table (cron-job does the job for this)

id       | day              | base_currency | currency | exchange |
---------|------------------|---------------|----------|----------|
1        | 2021-10-03       | EUR           | USD      | 1.19     |
2        | 2021-10-04       | EUR           | USD      | 1.17     |
3        | 2021-10-05       | EUR           | USD      | 1.16     |

I have to sum the orders total in USD and with the correct exchange rate!

How could I achieve that? Is there a common practice?

What I have so far is a basic sum:

sum(total_price) as salesRevenue,

Answer

The link you need is that based on the date. For that purpose I’d first run a check to see whether I do have all the necessary information.

So I’ll ask, given all the order dates, what rates are available for that day; requiring that the rate is NULL, meaning that there is no rate. The desired result is that the query returns nothing (no days have no rates, so all days have at least one rate).

SELECT o.order_date 
   FROM orders AS o 
   LEFT JOIN rates AS r 
       ON (o.order_date = r.day AND base_currency='EUR' AND currency='USD')
   WHERE r.exchange IS NULL;

Another acceptance test I’d run is to verify that there are no double entries for the exchange rate (each day has exactly one rate):

SELECT day, COUNT(*) AS n FROM rates
    GROUP BY day
    HAVING COUNT(*) > 1;

Again the desired result is that the query returns nothing.

Should the tests report anything, you wouldn’t be able to run the queries below, because you’d get wrong results.

If the tests both report nothing, then you can repeat almost the same JOIN and each tuple will have the exchange column available for use:

SELECT SUM(o.order_amount * usd.exchange) AS TotalUSD
   FROM orders AS o 
   -- Now we know a link is always available, we can use a JOIN
   JOIN rates AS usd
       ON (o.order_date = usd.day AND usd.base_currency='EUR' AND usd.currency='USD')
   ;

If you wanted to add a further currency column you would need to repeat the JOIN with a different alias:

SELECT SUM(o.order_amount * usd.exchange) AS TotalUSD,
       SUM(o.order_amount * sek.exchange) AS TotalSEK,
   FROM orders AS o 
   JOIN rates AS usd
       ON (o.order_date = usd.day AND base_currency='EUR' AND currency='USD')
   JOIN rates AS sek
       ON (o.order_date = sek.day AND sek.base_currency='EUR' AND sek.currency='SEK')
   ;