The cohort definition part is pretty clean, just a subquery or CTE filtering on 2023 spend.
Break the problem into two stages: first, aggregate 2023 spend per advertiser to identify those exceeding $1,000; second, compute the cohort's 2024 spend and the platform's total 2024 spend, then calculate the share. Use a subquery or CTE to filter the cohort, and ensure you handle edge cases like advertisers with no 2024 spend.
Pro tip: Clarify whether 'total 2023 spend' means calendar year or trailing twelve months, and confirm that 'platform spend' refers to the sum of daily spend across all advertisers. Also, consider if spend is in dollars or another currency.
Aggregate the daily spend table for 2023 by advertiser, summing spend, and filter for advertisers with total spend > $1,000.
For the eligible advertisers, sum their 2024 spend from the daily spend table.
Sum all 2024 spend from the daily spend table across all advertisers.
Divide cohort 2024 spend by total 2024 platform spend to get the percentage, and count the number of eligible advertisers.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Start by clarifying the schema and definitions (ad creation source, advertiser types to exclude, AI-assisted vs manual spend, YoY comparison). Then outline a query structure using CTEs: one for monthly spend by source with exclusions, and another for the cannibalization subset, joined or unioned appropriately. Finally, discuss validation and potential pitfalls like time zone, currency, and data completeness.
Pro tip: Mention that you would first check data quality and define 'AI-assisted' precisely (e.g., based on a flag or model), and consider using a window function to compute YoY changes within the same query for efficiency.
Ask clarifying questions about the schema, what constitutes 'ad creation source', which advertiser types to exclude, and how AI-assisted vs manual spend is identified. Confirm the exact YoY comparison (e.g., 2024 vs 2023).
Write a CTE to aggregate monthly spend for 2024 by ad creation source, filtering out excluded advertiser types. Ensure proper date truncation to month and grouping by source.
Create a second CTE that calculates year-over-year change in AI-assisted and manual spend per advertiser, then filter to advertisers where AI-assisted spend increased and manual spend decreased. Aggregate their monthly 2024 spend by source.
Join or union the base aggregation with the cannibalization subset, ensuring each month includes both total spend by source and the subset spend. Use appropriate labels to distinguish the two.
Check for data completeness, time zone consistency, and currency conversion. Discuss how to handle advertisers with no prior year data or missing source information.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.