Quick Overview

This question evaluates proficiency in data manipulation and analytical SQL/Python skills, including aggregation, date truncation, joins, window functions, and period-over-period comparisons for revenue metrics.

Compute Daily Revenue by Creation Source

Company: Meta

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Onsite

active_ads date | ad_id | advertiser_id | creation_source | revenue 2023-09-01 | 1001 | 17 | mobile_app | 150.00 2023-09-01 | 1002 | 18 | api | 75.00 2023-09-02 | 1003 | 17 | web | 200.00 ​ advertiser_info advertiser_id | advertiser_name | advertiser_country 17 | Alpha Corp | US 18 | Beta Ltd | UK 19 | Gamma GmbH | DE ##### Scenario Advertising revenue reporting for a multi-source ads platform using relational data in tables active_ads and advertiser_info. ##### Question Q1. Write a query to compute daily total revenue grouped by creation_source. Q2. Write a query to list the countries of the ten advertisers with the fewest active ads (break ties arbitrarily). Q3. For each creation_source, compute the proportion of advertisers whose spend in the current year exceeds their spend in the previous year by at least 1,000 USD. ##### Hints Use date truncation, aggregation, LEFT JOINs, window functions or CTEs for yearly spend, then COUNTIF / COUNT ratio.

Overview: This question evaluates proficiency in data manipulation and analytical SQL/Python skills, including aggregation, date truncation, joins, window functions, and period-over-period comparisons for revenue metrics.

Daily Total Revenue by Creation Source

You are given an ads revenue table `active_ads`. Write a SQL query to compute the daily total revenue grouped by `creation_source`. Return one row per (ad_date, creation_source) with the summed revenue for that date and source.

Tables

active_ads(ad_date DATE, ad_id INT, advertiser_id INT, creation_source VARCHAR(20), revenue DECIMAL(10,2))

Hints

  1. Group by both the date and creation_source.
  2. Use SUM(revenue) for totals.

Countries of the 10 Advertisers with the Fewest Active Ads

You are given tables `active_ads` and `advertiser_info`. Write a SQL query to list the countries of the ten advertisers with the fewest active ads. Count active ads as the number of distinct `ad_id` values in `active_ads` per advertiser. Include advertisers with zero active ads. Break ties arbitrarily. Return (advertiser_id, advertiser_country, active_ad_count) for the 10 advertisers with the smallest counts.

Tables

advertiser_info(advertiser_id INT, advertiser_name VARCHAR(100), advertiser_country VARCHAR(2))

active_ads(ad_date DATE, ad_id INT, advertiser_id INT, creation_source VARCHAR(20), revenue DECIMAL(10,2))

Hints

  1. Use a LEFT JOIN to keep advertisers with 0 ads.
  2. COUNT(DISTINCT ad_id) counts ads per advertiser.

YoY Advertiser Growth Proportion by Creation Source (2025 vs 2024)

Assume the current date is 2025-06-01. Using `active_ads`, for each `creation_source`, compute the proportion of advertisers whose spend (sum of revenue) in 2025 exceeds their spend in 2024 by at least 1,000 USD. Definitions: - 2025 spend = sum(revenue) for rows with ad_date between 2025-01-01 and 2025-12-31. - 2024 spend = sum(revenue) for rows with ad_date between 2024-01-01 and 2024-12-31. - Consider an advertiser part of a creation_source group if they have any spend in 2024 or 2025 for that creation_source. - Treat missing spend in a year as 0. Return one row per creation_source with: - total_advertisers - advertisers_meeting_threshold - proportion_meeting_threshold (advertisers_meeting_threshold / total_advertisers as a decimal).

Tables

active_ads(ad_date DATE, ad_id INT, advertiser_id INT, creation_source VARCHAR(20), revenue DECIMAL(10,2))

Hints

  1. Compute yearly spend per (creation_source, advertiser_id) using conditional aggregation.
  2. Treat missing year spend as 0 by using ELSE 0 in SUM(CASE...).

Loading coding console...