Straightforward join-and-aggregate once you see it.
First, clarify the table schemas and the grain of each table (e.g., daily ad revenue per currency, exchange rates per currency per date). Then write a SQL query that joins the revenue table to the exchange rate table on currency and date, multiplies revenue by the rate, and sums the converted amounts to get total USD revenue.
Pro tip: Always confirm whether exchange rates are quoted as USD per local currency or local currency per USD, and whether to use the rate on the revenue date or a period-average rate—this shows you understand real-world data nuances and can prevent costly errors.
Ask for or state the columns and granularity of both tables (e.g., revenue: date, currency, amount; exchange_rates: date, currency, rate_to_usd). Confirm the direction of the exchange rate.
Identify that you need to join on currency and date (or the appropriate time period). Decide between inner join (to only include dates with rates) or left join (to keep all revenue, handling missing rates).
Multiply the local revenue amount by the exchange rate to convert to USD. If the rate is USD per local currency, multiply; if local per USD, divide.
Use SUM() on the converted amounts to get the total ad revenue in USD. Optionally group by date or other dimensions if needed.
Check for missing exchange rates, duplicate rates, or currency mismatches. Consider using COALESCE or filtering to ensure accurate results.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.