Convert multi-currency revenue to USD totals

Q: Convert multi-currency revenue to USD totals

This is a Data Manipulation (SQL/Python) interview question from Meta for Data Scientist roles. View the full question and solution on PracHub.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

Question

Loading...

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

Convert multi-currency revenue to USD totals

Tables

Definitions / assumptions

Task

Comments (0)