Katkat
Back

Analyze Thirty-Day Ad Performance with SQL

MetaData Scientist · Onsite
MediumData ScientistOnsiteData Manipulation (SQL/Python)

Last updated

This problem tests proficiency in window functions, time-based aggregation, and join logic to analyze multi-dimensional advertising performance metrics.

Problem Statement

You are provided with two tables: ad_impressions and ad_conversions.

ad_impressions:

  • impression_id (int, primary key)
  • ad_id (int)
  • campaign_id (int)
  • ad_type (varchar: 'video', 'display', 'search')
  • impression_time (timestamp)
  • user_id (int)

ad_conversions:

  • conversion_id (int, primary key)
  • impression_id (int, foreign key)
  • conversion_value (decimal)
  • conversion_time (timestamp)

Tasks

  1. Identify the top 3 ad_ids by total impressions in the last 30 days.
  2. Calculate the Click-Through-Rate (CTR) per ad_type for the last 30 days. Note: CTR is defined as count(conversions) / count(impressions).
  3. Calculate the average conversion_value per unique user for each campaign_id.
  4. Find the hour of the day (0-23) that sees the highest number of total impressions across all ads.

Constraints

  • Assume current time is 2023-10-31 23:59:59.
  • impression_time is in UTC.

Example

If an ad appears 100 times and results in 5 conversions, the CTR is 0.05.

-- Your SQL query should return a result set with the appropriate columns for each task.

Hints

Hint 1

Use `CURRENT_DATE - INTERVAL '30 days'` to filter the impression data consistently.

Hint 2

For CTR, perform a `LEFT JOIN` on `impression_id` to ensure you count all impressions even if they have zero conversions.

Hint 3

When aggregating by hour, use `EXTRACT(HOUR FROM impression_time)` to group records regardless of the date.

Follow-up questions

  • How would you handle timezones if the users were distributed globally and you wanted to measure 'local' peak hours?
  • If the conversion attribution window was limited to 7 days post-impression, how would you modify the JOIN condition?
  • What potential bias might occur if you only calculate average conversion value per conversion, rather than per unique exposed user?