Compute 7-Day Rolling Average of Unique Post Viewers

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%'.

7-Day Rolling Average and Hashtag Filter

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.

Table POSTS

column	type	constraints
post_id	INTEGER	PRIMARY KEY, NOT NULL
content	TEXT	NOT NULL
hashtags	TEXT	NOT NULL

Sample data:

content	hashtags	post_id
Apple launch video	#Apple #iPhone #Launch	10
Recipe tutorial	#Food #Recipe	11
Travel vlog	#Travel #Adventure	12
Tech news	#Tech #Apple	13
Fitness tips	#Health	14

Table POST_VIEWS

column	type	constraints
user_id	INTEGER	NOT NULL
view_date	DATE	NOT NULL
post_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES POSTS(post_id)

Sample data:

post_id	user_id	view_date
10	101	2023-08-01
11	102	2023-08-01
10	101	2023-08-02
12	103	2023-08-03
10	104	2023-08-03
13	105	2023-08-02
13	106	2023-08-02
10	104	2023-08-01

Expected output

For search term 'Apple', posts 10 (#Apple ...) and 13 (#Tech #Apple) match. Returns each matched post's daily distinct-viewer count plus its per-post 7-day rolling average (rounded to 2dp), ordered by post_id then view_date.

daily_unique_viewers	post_id	rolling_avg_7d	view_date
2	10	2.00	2023-08-01
1	10	1.50	2023-08-02
1	10	1.33	2023-08-03
2	13	2.00	2023-08-02

Hints

1. Match the hashtag with ILIKE '%' || 'Apple' || '%' (Postgres case-insensitive LIKE) so the term can appear anywhere in the free-text field.
2. Aggregate to one row per (post_id, view_date) with COUNT(DISTINCT user_id) FIRST, then apply the rolling window on that aggregated result.
3. For a 7-calendar-day window, use a window frame RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW partitioned by post_id and ordered by view_date — RANGE with an interval respects date gaps, unlike ROWS.