Coding questions · Meta
Calculate Total Revenue in USD Using SQL Query
This question evaluates proficiency in SQL data manipulation, focusing on joins, aggregations, and handling currency conversion across relational tables.
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.
Total Advertising Revenue in USD
Given the ads_revenue table with revenue stored in various currencies and the exchange_rate table with conversion rates to USD as of 2025-06-01, write a SQL query that returns the total advertising revenue converted to USD.
Table ads_revenue
| column | type | constraints |
|---|---|---|
| ad_id | INTEGER | PRIMARY KEY, NOT NULL |
| country | VARCHAR(2) | NOT NULL |
| revenue | DECIMAL(12,2) | NOT NULL |
| currency | VARCHAR(3) | NOT NULL, FOREIGN KEY REFERENCES exchange_rate(currency) |
Sample data:
| ad_id | country | currency | revenue |
|---|---|---|---|
| 101 | US | USD | 120 |
| 102 | DE | EUR | 90 |
| 103 | JP | JPY | 13000 |
| 104 | IN | INR | 7000 |
| 105 | FR | EUR | 85 |
Table exchange_rate
| column | type | constraints |
|---|---|---|
| currency | VARCHAR(3) | PRIMARY KEY, NOT NULL |
| usd_rate | DECIMAL(12,6) | NOT NULL |
Sample data:
| currency | usd_rate |
|---|---|
| USD | 1 |
| EUR | 1.08 |
| JPY | 0.0065 |
| INR | 0.012 |
Expected output
A single-row result containing the total revenue converted to USD by joining ads_revenue with exchange_rate on currency, multiplying revenue by usd_rate, and summing across all rows.
| total_revenue_usd |
|---|
| 477.5 |
Hints
- Join ads_revenue to exchange_rate on the currency column.
- Multiply revenue by usd_rate and SUM the result; ROUND to 2 decimals for USD.