Company: Meta | Difficulty: hard
Meta's Ads Measurement team credits app-install conversions to the advertiser most likely responsible for them.
An ad_interactions row (an impression or a click) can only be credited for an app_conversions row from the same user_id, and only if the interaction_time is no more than 7 days before the conversion_time (i.e., conversion_time minus interaction_time is between 0 and 7 days, both bounds inclusive). Among all interactions that qualify for a given conversion, credit goes to whichever one is closest in time to the conversion. If two or more qualifying interactions are tied on that time gap, prefer a click over an impression; if still tied, prefer the interaction with the lower advertiser_id. Each conversion can credit at most one advertiser; if no interaction qualifies, the conversion is left unattributed and excluded entirely.
ad_interactions
impression
click
app_conversions
user_id
interaction_time
conversion_time
advertiser_id
For every advertiser credited with at least one conversion, report the advertiser_name, the count of conversions credited to them as total_attributed_conversions, and the sum of conversion_value across those conversions as total_attributed_revenue. Treat a NULL conversion_value as contributing 0 to total_attributed_revenue while still counting toward total_attributed_conversions. Round total_attributed_revenue to 2 decimal places. Order the results by total_attributed_revenue descending, breaking ties by advertiser_name ascending.
advertiser_name
total_attributed_conversions
conversion_value
total_attributed_revenue
NULL
0
advertisers
interaction_id
interaction_type
conversion_id
For conversion 1 (user_id 501, converted 2024-06-04 08:00:00), three interactions fall within the 7-day window: Duolingo's impression (Jun 1), Duolingo's click (Jun 3), and HelloFresh's impression (Jun 2). Duolingo's click on Jun 3 is closest in time (about 18 hours before conversion), so Duolingo is credited the full 25.00. For conversion 2 (user_id 502), HelloFresh's click on Jun 10 is the only qualifying interaction, so HelloFresh is credited 40.00.
1
501
2024-06-04 08:00:00
25.00
2
502
40.00
DROP TABLE IF EXISTS app_conversions; DROP TABLE IF EXISTS ad_interactions; DROP TABLE IF EXISTS advertisers; CREATE TABLE advertisers (advertiser_id INT, advertiser_name VARCHAR(255)); CREATE TABLE ad_interactions (interaction_id INT, advertiser_id INT, user_id INT, interaction_type VARCHAR(20), interaction_time TIMESTAMP); CREATE TABLE app_conversions (conversion_id INT, user_id INT, conversion_time TIMESTAMP, conversion_value DECIMAL(10,2)); INSERT INTO advertisers VALUES (101, 'Duolingo'), (102, 'HelloFresh'); INSERT INTO ad_interactions VALUES (1, 101, 501, 'impression', '2024-06-01 09:00:00'), (2, 101, 501, 'click', '2024-06-03 14:00:00'), (3, 102, 501, 'impression', '2024-06-02 10:00:00'), (4, 102, 502, 'click', '2024-06-10 12:00:00'); INSERT INTO app_conversions VALUES (1, 501, '2024-06-04 08:00:00', 25.00), (2, 502, '2024-06-11 12:00:00', 40.00);
← All SQL Challenges · Meta Questions · Learn SQL Free