Analyze Ad CTR and Convert Transactions to USD

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.

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.

Table AdsImpressions

column	type	constraints
user_id	INTEGER
ad_id	INTEGER
impressions	INTEGER
clicks	INTEGER
event_dt	DATE

Sample data:

ad_id	clicks	event_dt	impressions	user_id
55	3	2023-09-01	12	101
77	0	2023-09-01	7	102
77	2	2023-09-02	5	101
55	1	2023-09-02	9	103
88	4	2023-09-03	11	104

Expected output

One row per user with the ad that has the highest CTR during 2023-09-01 through 2023-09-07, ties broken by smallest ad_id.

ctr	top_ad_id	user_id
0.4	77	101
0	77	102
0.1111	55	103
0.3636	88	104

Hints

1. Aggregate clicks and impressions per user and ad, then rank within each user by CTR.
2. 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.

Table ExchangeRates

column	type	constraints
currency	VARCHAR(3)
rate_to_usd	DECIMAL(10,4)
rate_dt	DATE

Sample data:

currency	rate_dt	rate_to_usd
USD	2023-09-01	1
EUR	2023-09-01	1.1
JPY	2023-09-01	0.0068
EUR	2023-09-02	1.12
JPY	2023-09-02	0.0069

Table Transactions

column	type	constraints
txn_id	INTEGER
user_id	INTEGER
amount	DECIMAL(12,2)
currency	VARCHAR(3)
txn_dt	DATE

Sample data:

amount	currency	txn_dt	txn_id	user_id
25	EUR	2023-09-01	5001	101
3000	JPY	2023-09-01	5002	102
10	USD	2023-09-02	5003	101
15	EUR	2023-09-02	5004	103
5000	JPY	2023-09-03	5005	104

Expected output

All transactions with amounts converted to USD using the latest exchange rate on or before the transaction date; USD defaults to 1.0 if missing.

amount	amount_usd	currency	txn_dt	txn_id	user_id
25	27.5	EUR	2023-09-01	5001	101
3000	20.4	JPY	2023-09-01	5002	102
10	10	USD	2023-09-02	5003	101
15	16.8	EUR	2023-09-02	5004	103
5000	34.5	JPY	2023-09-03	5005	104

Hints

1. Join on currency and rate_dt <= txn_dt, then pick the most recent rate using ROW_NUMBER.
2. Use COALESCE to default USD to 1.0 when no rate is found and ROUND to 2 decimals.