Write SQL for geo posting-frequency drops
Using the schema below, write a single ANSI SQL query (window functions allowed) that identifies countries with the largest share of creators whose posting frequency dropped >30% in the last 7 complete days relative to the prior 28 days. Use creators’ local time zones to determine calendar days. Exclude test/deleted posts and non‑active creators (status ≠ 'active'). Define “today” as 2025‑09‑01 and do not use current_date(); use static literals.
Windows (creator local dates):
- Last7 = 2025‑08‑25 to 2025‑08‑31 (inclusive)
- Base28 = 2025‑07‑28 to 2025‑08‑24 (inclusive)
Definitions:
- freq_last7 = posts in Last7 / 7
- freq_base28 = posts in Base28 / 28
- A creator is a “dropper” if freq_last7 < 0.7 * freq_base28 and the creator had ≥1 valid post in Base28 (so brand‑new creators don’t inflate drops).
- Active creators for denominator = creators with status='active' and ≥1 valid post in Base28.
Output one row per country with: country, total_active_creators, dropping_creators, share_dropping (decimal), median_post_interval_days_last7 (the median time in days between consecutive valid posts in Last7, computed across that country’s valid posts), sorted by share_dropping desc then total_active_creators desc, and return only the top 10 countries.
Assume a warehouse that supports time zone conversion (e.g., CONVERT_TIMEZONE or FROM_UTC_TIMESTAMP). If not available, show how you’d approximate with tz offsets.
Schema and small sample data: creators +------------+---------+----------------------+--------+ | creator_id | country | tz | status | +------------+---------+----------------------+--------+ | 1 | US | America/Los_Angeles | active | | 2 | US | America/New_York | active | | 3 | JP | Asia/Tokyo | active | | 4 | BR | America/Sao_Paulo | active | | 5 | DE | Europe/Berlin | banned | +------------+---------+----------------------+--------+
posts +---------+------------+---------------------+---------+------------+ | post_id | creator_id | created_at_utc | is_test | is_deleted | +---------+------------+---------------------+---------+------------+ | 101 | 1 | 2025-08-26 23:30:00 | 0 | 0 | | 102 | 1 | 2025-08-27 08:10:00 | 0 | 0 | | 103 | 2 | 2025-08-29 01:05:00 | 1 | 0 | | 104 | 2 | 2025-08-30 13:40:00 | 0 | 0 | | 105 | 3 | 2025-08-20 09:00:00 | 0 | 1 | | 106 | 3 | 2025-08-31 23:55:00 | 0 | 0 | | 107 | 4 | 2025-08-05 16:30:00 | 0 | 0 | | 108 | 4 | 2025-08-28 22:05:00 | 0 | 0 | | 109 | 4 | 2025-09-01 01:10:00 | 0 | 0 | | 110 | 1 | 2025-07-30 11:00:00 | 0 | 0 | +---------+------------+---------------------+---------+------------+
Explain any assumptions you make (e.g., handling creators with <2 posts for interval calculation), and ensure your query is deterministic with respect to the stated windows.