Calculate Total Revenue in USD Using SQL Query

ads_revenue

+---------+------------+---------+----------+ | ad_id | country | revenue | currency | +---------+------------+---------+----------+ | 101 | US | 120.00 | USD | | 102 | DE | 90.00 | EUR | | 103 | JP | 13000 | JPY | | 104 | IN | 7000 | INR | | 105 | FR | 85.00 | EUR | +---------+------------+---------+----------+

exchange_rate

+----------+----------+ | currency | usd_rate | +----------+----------+ | USD | 1.00 | | EUR | 1.08 | | JPY | 0.0065 | | INR | 0.012 | +----------+----------+

Scenario

An ad-tech team stores revenue generated from ad impressions in various currencies and needs total revenue in USD for daily reporting.

Question

Write a SQL query that returns the total advertising revenue converted to USD, using the revenue and currency columns in ads_revenue and today’s rates in exchange_rate.

Hints

Join ads_revenue with exchange_rate, multiply revenue by usd_rate, then SUM.