Compute 7-Day Rolling Average of Unique Post Viewers
Company: TikTok
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
POST_VIEWS
+---------+------------+---------+
| user_id | view_date | post_id |
| 101 | 2023-08-01 | 10 |
| 102 | 2023-08-01 | 11 |
| 101 | 2023-08-02 | 10 |
| 103 | 2023-08-03 | 12 |
| 104 | 2023-08-03 | 10 |
POSTS
+---------+----------------------+-------------------------+
| post_id | content | hashtags |
| 10 | 'Apple launch video' | '#Apple #iPhone #Launch'|
| 11 | 'Recipe tutorial' | '#Food #Recipe' |
| 12 | 'Travel vlog' | '#Travel #Adventure' |
| 13 | 'Tech news' | '#Tech #Apple' |
| 14 | 'Fitness tips' | '#Health' |
##### Scenario
A social-media analytics team has two tables: POST_VIEWS records every user’s daily post views, and POSTS stores post metadata including a free-text hashtags field.
##### Question
Write an SQL query to compute, for every post, the 7-day rolling average of daily unique viewers ordered by view_date. Given a search term (e.g. 'Apple'), return all post_id values whose hashtags column contains that term (case-insensitive). State the logical execution order of SQL clauses (e.g. FROM, WHERE, GROUP BY…) and explain why knowing this order matters when debugging or optimizing queries.
##### Hints
Use COUNT(DISTINCT user_id) over a 7-day RANGE window; apply ILIKE or LOWER(hashtags) LIKE '%apple%'.
Overview: This question evaluates proficiency with SQL data manipulation, window functions for rolling aggregates, deduplication of user events, and text-filtering of metadata.
You are given two tables:
- **`POSTS`** — post metadata, including a free-text `hashtags` field (e.g. `'#Apple #iPhone #Launch'`).
- **`POST_VIEWS`** — one row per (user, post, day) view event.
Write a **single PostgreSQL query** that does the following:
1. Find every post whose `hashtags` field contains the term **`Apple`**, matched **case-insensitively** (a post matches if `Apple` appears anywhere in its hashtags, e.g. both `'#Apple #iPhone'` and `'#Tech #Apple'`).
2. Restricting to those matched posts only, for each `(post_id, view_date)` compute the **daily count of distinct viewers**.
3. For each of those daily rows, also compute the **7-day rolling average** of the daily distinct-viewer count — i.e. the average daily-unique-viewer value over the window from 6 days before the current `view_date` up to and including the current `view_date`, computed **per post**.
**Required output:** one row per `(post_id, view_date)` that has at least one view among the matched posts, with these columns:
- `post_id`
- `view_date`
- `daily_unique_viewers` — distinct users who viewed that post on that date
- `rolling_avg_7d` — the 7-day (calendar) rolling average of `daily_unique_viewers`, rounded to 2 decimal places
**Sort order:** ascending by `post_id`, then by `view_date`.
> Note: the original prompt used a `:search_term` parameter; it is pinned here to the literal value **`Apple`** so the result is concrete and gradable.
Tables
POSTS(post_id INTEGER, content TEXT, hashtags TEXT)
POST_VIEWS(user_id INTEGER, view_date DATE, post_id INTEGER)
Hints
- Match the hashtag with `ILIKE '%' || 'Apple' || '%'` (Postgres case-insensitive `LIKE`) so the term can appear anywhere in the free-text field.
- Aggregate to one row per (post_id, view_date) with `COUNT(DISTINCT user_id)` FIRST, then apply the rolling window on that aggregated result.