Katkat
Back

Write SQL for CTR and Revenue

MetaData Scientist · Technical Screen
MediumData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

This question tests the ability to join event logs with reference data, perform conditional aggregation to calculate click-through rates (CTR), and apply date-sensitive currency conversions.

Scenario

You are analyzing user engagement data for a global platform. You have two tables: events and currency_rates.

Tables

events table:

  • event_id (int)
  • user_id (int)
  • event_type (string: 'view', 'click')
  • event_timestamp (datetime)
  • revenue_usd (float, only present for 'click' events, else 0)
  • country_code (string)

currency_rates table:

  • country_code (string)
  • currency_code (string)
  • conversion_rate_to_usd (float)
  • effective_date (date)

Task

Write a SQL query to output the following metrics per hour and per country:

  1. CTR: The ratio of clicks to views (clicks / views).
  2. Total Revenue: Sum of revenue converted to the local currency using the latest available exchange rate for that country.

Constraints:

  • Filter for events occurring in the last 7 days.
  • If a country has no views, treat the CTR as 0.
  • Group data by hour (e.g., '2023-10-01 14:00:00').

Example

Input: Events: (1, 101, 'view', '2023-10-01 14:05:00', 0, 'US'), (2, 101, 'click', '2023-10-01 14:10:00', 2.0, 'US') Rates: ('US', 'USD', 1.0, '2023-10-01')

Output: hour, country_code, ctr, total_revenue_local 2023-10-01 14:00:00, US, 1.0, 2.0

Edge Cases

  • Handle missing currency rates (assume 1.0 if not found).
  • Ensure division by zero is handled when calculating CTR.

Hints

Hint 1

Use date truncation functions to bucket the timestamps into hourly intervals.

Hint 2

Use conditional aggregation (CASE statements within SUM) to count views and clicks separately for each group.

Hint 3

Use a window function or a subquery to join the currency rates table against the latest date per country to ensure the most accurate conversion.

Follow-up questions

  • How would your query change if you needed to calculate the moving average of CTR over the last 24 hours?
  • What steps would you take if the `events` table was significantly larger and the query performance became an issue?
  • How would you handle cases where currency conversion rates change mid-day?