Calculate Total Revenue in USD Using SQL Query
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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.
Overview: This question evaluates proficiency in SQL data manipulation, focusing on joins, aggregations, and handling currency conversion across relational tables.
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.
Tables
ads_revenue(ad_id INTEGER, country VARCHAR(2), revenue DECIMAL(12,2), currency VARCHAR(3))
exchange_rate(currency VARCHAR(3), usd_rate DECIMAL(12,6))
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.