Compute daily active ads
Company: Meta
Role: Analytics Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Onsite
## SQL: Daily active ads
You are working on an ads platform.
### Tables
**ads**
- `ad_id` BIGINT (PK)
- `advertiser_id` BIGINT
- `status` STRING -- one of ('ACTIVE','PAUSED','DELETED')
- `created_at` TIMESTAMP (UTC)
- `ended_at` TIMESTAMP (UTC, nullable)
**ad_events**
- `event_time` TIMESTAMP (UTC)
- `ad_id` BIGINT (FK → ads.ad_id)
- `event_type` STRING -- one of ('IMPRESSION','CLICK','CONVERSION')
- `user_id` BIGINT
### Definition
An ad is **active on a given day** if:
1) its `status = 'ACTIVE'` on that day, and
2) it received **at least 1 impression** (`event_type='IMPRESSION'`) on that day.
### Task
Write a SQL query that returns the last **30 days** (including today) with:
- `day` (DATE, UTC)
- `active_ads` (count of distinct `ad_id` active that day)
Assume the SQL dialect supports common table expressions and `DATE(event_time)` casting.
Quick Answer: This question evaluates proficiency in data manipulation and analytics engineering, focusing on time-based event aggregation, status-based filtering, and distinct counting to compute daily active ad metrics.