Compute 7-Day Rolling Average of Unique Post Viewers

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Onsite rounds at TikTok.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at TikTok during technical interviews.

Question

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

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

PracHub · Accepted Answer

This question evaluates proficiency with SQL data manipulation, window functions for rolling aggregates, deduplication of user events, and text-filtering of metadata.

Quick Overview

Compute 7-Day Rolling Average of Unique Post Viewers