Katkat
Back

Compute daily active ads

MetaAnalytics Engineer · Onsite
EasyAnalytics EngineerOnsiteData Manipulation (SQL/Python)

Last updated

This question tests your ability to aggregate time-series event data and perform distinct counts based on state transitions.

Problem Statement

You are given a table ad_events that records user interactions with advertisements. Your goal is to identify how many unique ads were 'active' on each day. An ad is considered 'active' on a given day if it received at least one 'click' or 'impression' event on that day.

Schema

ad_events table:

  • ad_id (int): Unique identifier for the advertisement.
  • event_type (string): Type of event ('click', 'impression', or 'view').
  • event_date (date): The date the event occurred.

Task

Write a SQL query to return the event_date and the count of unique ad_ids that were active on that day. Filter out the 'view' event type as it does not count toward ad activity.

Example

Input:

ad_idevent_typeevent_date
101click2023-10-01
101view2023-10-01
102impression2023-10-01
101view2023-10-02

Output:

event_dateactive_ad_count
2023-10-012
2023-10-020

Constraints

  • event_date is in the format 'YYYY-MM-DD'.
  • Exclude any event_type that is not 'click' or 'impression'.
  • Return results ordered by event_date ascending.

Hints

Hint 1

Start by filtering the table to exclude rows where event_type is 'view'.

Hint 2

Group the remaining rows by event_date to isolate activity per day.

Hint 3

Use COUNT(DISTINCT ad_id) to calculate the number of unique active ads for each day.

Follow-up questions

  • How would you modify this query if you also needed to include dates where zero ads were active?
  • If the table size grew to billions of rows, what strategies would you use to optimize this calculation?
  • How would you compute the 7-day rolling average of daily active ads?