Convert multi-currency revenue to USD totals
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
You are analyzing ad revenue recorded in multiple currencies and need to compute **US revenue** and **global revenue** in **USD**.
### Tables
Assume the following schemas:
**ad_revenue**
- `event_date` DATE
- `country` STRING -- ISO country code (e.g., `US`, `CA`)
- `currency` STRING -- ISO currency code (e.g., `USD`, `EUR`)
- `revenue_amount` NUMERIC -- amount in `currency`
**fx_rates**
- `rate_date` DATE
- `currency` STRING
- `usd_per_unit` NUMERIC -- 1 unit of `currency` equals this many USD
### Definitions / assumptions
- Convert revenue on `event_date` using the FX rate from the **same date** (`fx_rates.rate_date = ad_revenue.event_date`).
- If a rate is missing for a date, use the most recent prior rate (state how you handle this in SQL).
- **US revenue** = revenue from rows where `country = 'US'`.
- **Global revenue** = revenue from all countries.
### Task
Write a SQL query to output daily totals:
- `event_date`
- `us_revenue_usd`
- `global_revenue_usd`
Quick Answer: This question evaluates a candidate's competency in converting multi-currency revenue into USD totals by aligning event dates with FX rates, handling missing rate data, and performing date-based aggregation to produce accurate US and global revenue figures, covering skills in currency conversion, time-aware joins, and data integrity for financial metrics. It is commonly asked to assess accuracy in time-series financial aggregations and proficiency with data manipulation tools and query logic, and is categorized under Data Manipulation (SQL/Python) with a practical application emphasis rather than purely conceptual theory.