Start by clarifying definitions and assumptions, then build the query in logical steps: filter active creators and valid posts, bucket posts by local date, compute per-creator posting counts for the last 7 days and prior 28 days, identify creators with >30% drop, and finally aggregate by country to get the required metrics. Use CTEs to keep the query modular and readable, and ensure all date calculations use the static 'today' literal.
Pro tip: Explicitly state your assumptions about 'active creators' and 'posting frequency' (e.g., posts per day) and mention that you would validate the 30% drop threshold with stakeholders, as small sample sizes can lead to misleading shares. Also, consider using approximate percentiles for median if the dataset is large.
Filter to active creators (e.g., those with at least one post in the last 7 days) and exclude test/deleted posts. Ensure all date bucketing uses the creator's local timezone.
For each creator, calculate the number of posts in the last 7 complete days and the prior 28 days, using local date buckets. Derive daily posting rates for each period.
Flag creators whose posting frequency dropped by more than 30% (e.g., (prior_rate - recent_rate)/prior_rate > 0.3). Handle edge cases like zero prior posts.
Group by country to compute total active creators, number of dropping creators, share dropping, and median time between consecutive posts in the last 7 days (using window functions or percentile_cont).
Order countries by share dropping descending and limit to top 10. Include all required metrics in the final output.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.