The ex-ante vs ex-post distinction is where most of the complexity lives.
Clarify the schema and definitions first, then build a modular SQL solution: compute ex-ante and ex-post violation flags per view, aggregate daily prevalence by country, and finally rank creators by ex-post violating view share and break down by surface. Use CTEs to keep logic readable and testable.
Pro tip: Explicitly state your assumptions about the data (e.g., one moderation decision per content, view timestamps in UTC) and validate edge cases like multiple decisions or missing metadata before writing the final query.
Confirm table structures, join keys, and what constitutes a 'violating' decision (e.g., decision = 'violation'). Define ex-ante (decision timestamp <= view timestamp) and ex-post (any final violation decision) precisely.
Join views with moderation decisions and metadata. Create two flags: ex_ante_violation (1 if a violation decision existed at or before view time) and ex_post_violation (1 if the content was ever decided as violating).
Group by date, country, and surface (if needed) to compute total views and violating views. Calculate prevalence as violating views / total views for both ex-ante and ex-post over the 7-day window.
Aggregate views by creator, compute ex-post violating view share, and rank creators to find the top 3. Ensure you handle ties or minimum view thresholds if appropriate.
For the top 3 creators, group their ex-post violating views by surface and compute counts or shares. Present the breakdown clearly.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.