Find least active countries
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
You are given an ads activity table.
**Table: `ad_activity`**
- `ad_id` BIGINT — unique ad identifier
- `advertiser_id` BIGINT — advertiser that owns the ad
- `country` VARCHAR — advertiser country
- `activity_date` DATE — activity date in UTC
- `is_active` BOOLEAN — whether the ad was active on that date
**Definitions**
- `active_ads_last_year` = number of distinct `ad_id` values with at least one record where `is_active = TRUE` during calendar year 2023.
- `active_ads_this_year` = number of distinct `ad_id` values with at least one record where `is_active = TRUE` during calendar year 2024.
- Only include countries where `active_ads_last_year > 1000`.
**Task**
Return the 10 countries with the smallest `active_ads_this_year` among the eligible countries.
**Required output columns**
- `country`
- `active_ads_this_year`
- `active_ads_last_year`
Sort by `active_ads_this_year` ascending, then `country` ascending.
Quick Answer: This question evaluates proficiency in SQL-based data manipulation and analytics, focusing on time-based filtering, distinct aggregation, grouping, thresholded inclusion, and producing ordered, limited result sets across calendar-year comparisons.