Write SQL for geo posting-frequency drops
Company: TikTok
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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.
Overview: This question evaluates proficiency in SQL-based time-series data manipulation, including time zone–aware date normalization, window functions, aggregation, filtering and median computations within the Data Manipulation (SQL/Python) domain.
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:
```sql
(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.
Tables
creators(creator_id INT, country VARCHAR(2), tz VARCHAR(64), status VARCHAR(16))
posts(post_id INT, creator_id INT, created_at_utc TIMESTAMP, is_test INT, is_deleted INT)
Hints
- 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.
- 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.