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.
Overview: 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.
Top CTR Ad Per User
Using the AdsImpressions table, compute click-through rate (CTR = clicks / impressions) aggregated per user and ad over the fixed 7-day window from DATE '2023-09-01' to DATE '2023-09-07' (inclusive). Return, for each user, the ad with the highest CTR; break ties by the smallest ad_id. Output columns: user_id, top_ad_id, ctr rounded to 4 decimals.
Tables
AdsImpressions(user_id INTEGER, ad_id INTEGER, impressions INTEGER, clicks INTEGER, event_dt DATE)
Hints
- Aggregate clicks and impressions per user and ad, then rank within each user by CTR.
- Cast to DECIMAL to avoid integer division; round CTR to 4 decimals.
Convert Transactions to USD
For each row in Transactions, convert amount to USD using ExchangeRates by choosing the most recent rate on or before txn_dt for the same currency. If currency is 'USD' and no rate exists, assume 1.0. Output txn_id, user_id, amount, currency, txn_dt, and amount_usd rounded to 2 decimals. Order by txn_id.
Tables
ExchangeRates(currency VARCHAR(3), rate_to_usd DECIMAL(10,4), rate_dt DATE)
Transactions(txn_id INTEGER, user_id INTEGER, amount DECIMAL(12,2), currency VARCHAR(3), txn_dt DATE)
Hints
- Join on currency and rate_dt <= txn_dt, then pick the most recent rate using ROW_NUMBER.
- Use COALESCE to default USD to 1.0 when no rate is found and ROUND to 2 decimals.