Analyze Ad CTR and Convert Transactions to USD
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Onsite
AdsImpressions
+-----------+---------+------------+--------+-----------+
| user_id | ad_id | impressions| clicks | event_dt |
+-----------+---------+------------+--------+-----------+
| 101 | 55 | 12 | 3 | 2023-09-01|
| 102 | 77 | 7 | 0 | 2023-09-01|
| 101 | 77 | 5 | 2 | 2023-09-02|
| 103 | 55 | 9 | 1 | 2023-09-02|
| 104 | 88 | 11 | 4 | 2023-09-03|
ExchangeRates
+-----------+------------+-----------+
| currency | rate_to_usd| rate_dt |
+-----------+------------+-----------+
| USD | 1.00 | 2023-09-01|
| EUR | 1.10 | 2023-09-01|
| JPY | 0.0068 | 2023-09-01|
| EUR | 1.12 | 2023-09-02|
| JPY | 0.0069 | 2023-09-02|
Transactions
+---------+---------+---------+----------+-----------+
| txn_id | user_id | amount | currency | txn_dt |
+---------+---------+---------+----------+-----------+
| 5001 | 101 | 25.00 | EUR | 2023-09-01|
| 5002 | 102 | 3000 | JPY | 2023-09-01|
| 5003 | 101 | 10.00 | USD | 2023-09-02|
| 5004 | 103 | 15.00 | EUR | 2023-09-02|
| 5005 | 104 | 5000 | JPY | 2023-09-03|
##### Scenario
E-commerce advertising analytics—query ad performance data and convert transaction amounts to USD.
##### Question
Write an SQL query that, for each user, returns the ad with the highest click-through-rate (CTR = clicks / impressions) over the last 7 days. Using the exchange_rates table, convert every transaction amount in transactions to its USD equivalent for the transaction date.
##### Hints
Consider window functions for CTR ranking and a date-based join for currency conversion.
Quick Answer: This question evaluates proficiency in calculating and ranking per-user ad click-through rates and normalizing transaction amounts across currencies, testing competencies in aggregation, ranking logic, temporal joins, and currency conversion.