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.

Countries with the largest share of creators whose posting frequency dropped >30%

Using the creators and posts tables below, write a single PostgreSQL query (CTEs and window functions allowed) that ranks countries by the share of their active creators whose posting frequency dropped by more than 30% in the last 7 complete days relative to the prior 28 days.

Use creators' local time zones. A post's calendar day is determined by converting posts.created_at_utc (a TIMESTAMP stored in UTC) into the creator's time zone (creators.tz) and taking the local DATE. In PostgreSQL, convert with:

(created_at_utc AT TIME ZONE 'UTC') AT TIME ZONE c.tz   -- local timestamp
((created_at_utc AT TIME ZONE 'UTC') AT TIME ZONE c.tz)::date   -- local date

Filters (a post is "valid" only if all hold):

• is_test = 0 (exclude test posts)
• is_deleted = 0 (exclude deleted posts)
• the post belongs to a creator with status = 'active' (non-active creators are excluded entirely)

Static windows (by the creator's local DATE, inclusive bounds) — do NOT use CURRENT_DATE:

• Last7: 2025-05-25 to 2025-05-31
• Base28: 2025-04-27 to 2025-05-24

Definitions:

• freq_last7 = (valid posts in Last7) / 7
• freq_base28 = (valid posts in Base28) / 28
• A creator is a dropper if freq_last7 < 0.7 * freq_base28 AND the creator has at least 1 valid post in Base28.
• The denominator for a country (total_active_creators) = active creators with at least 1 valid post in Base28.

Output — one row per country, exactly these columns:

• country
• total_active_creators — active creators with >= 1 valid Base28 post
• dropping_creators — how many of those are droppers
• share_dropping — dropping_creators / total_active_creators, as a decimal rounded to 4 places
• median_post_interval_days_last7 — the median number of days between a creator's consecutive valid posts within Last7, taken across all per-creator intervals in the country. Compute each creator's gaps with LAG over their local post dates inside Last7; creators with fewer than 2 valid Last7 posts contribute no interval (so a country with no such gaps yields NULL).

Sort by share_dropping DESC, then total_active_creators DESC, and return only the top 10 countries.

Table creators

column	type	constraints
creator_id	INT	PRIMARY KEY, NOT NULL
country	VARCHAR(2)	NOT NULL
tz	VARCHAR(64)	NOT NULL
status	VARCHAR(16)	NOT NULL

Sample data:

country	creator_id	status	tz
US	1	active	America/Los_Angeles
US	2	active	America/New_York
JP	3	active	Asia/Tokyo
BR	4	active	America/Sao_Paulo
DE	5	banned	Europe/Berlin

Table posts

column	type	constraints
post_id	INT	PRIMARY KEY, NOT NULL
creator_id	INT	NOT NULL
created_at_utc	TIMESTAMP	NOT NULL
is_test	INT	NOT NULL
is_deleted	INT	NOT NULL

Sample data:

created_at_utc	creator_id	is_deleted	is_test	post_id
2025-04-28 18:00:00	1	0	0	1001
2025-05-01 18:00:00	1	0	0	1002
2025-05-05 18:00:00	1	0	0	1003
2025-05-10 18:00:00	1	0	0	1004
2025-05-12 18:00:00	1	0	0	1005
2025-05-15 18:00:00	1	0	0	1006
2025-05-20 18:00:00	1	0	0	1007
2025-05-24 18:00:00	1	0	0	1008
2025-05-26 18:00:00	1	0	0	1009
2025-05-03 18:00:00	2	0	0	1010
2025-05-18 18:00:00	2	0	0	1011
2025-05-25 18:00:00	2	0	0	1012
2025-05-27 18:00:00	2	0	1	1013
2025-05-31 18:00:00	2	0	0	1014
2025-04-30 02:00:00	3	0	0	1015
2025-05-04 02:00:00	3	0	0	1016
2025-05-05 02:00:00	3	1	0	1017
2025-05-09 02:00:00	3	0	0	1018
2025-05-13 02:00:00	3	0	0	1019
2025-05-22 02:00:00	3	0	0	1020
2025-05-10 15:00:00	4	0	0	1021
2025-05-30 15:00:00	4	0	0	1022
2025-05-10 10:00:00	5	0	0	1023

Expected output

Top countries ranked by share of active creators (>=1 valid Base28 post) who are droppers, with the country-level median of per-creator consecutive-post gaps in Last7.

country	dropping_creators	median_post_interval_days_last7	share_dropping	total_active_creators
JP	1	NULL	1.0000	1
US	1	6	0.5000	2
BR	0	NULL	0.0000	1

Hints

1. Convert UTC to local with (created_at_utc AT TIME ZONE 'UTC') AT TIME ZONE tz, then cast to ::date for the day-window filters.
2. Count Base28 and Last7 valid posts per creator; only creators with base28_posts >= 1 are in the denominator and can be droppers. Cast to ::numeric so the frequency division isn't integer-truncated.
3. For the median interval, use LAG over local dates within Last7 per creator to get consecutive-post gaps, then PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY gap) per country.