Running Total Reviews With Window Functions | Dataford Interview Questions

Yelp's Data Science team wants to monitor recent review activity for each Yelp user. Write a PostgreSQL query that reports the number of reviews each user submitted on each active review date and the user's trailing 30-day review total.

Requirements

Join users to reviews and exclude reviews that do not belong to a registered user.
Aggregate multiple reviews by the same user on the same calendar date.
Use a window function partitioned by user and ordered by review date. The trailing window includes the current date and the preceding 29 calendar days.
Return results ordered by user and review date.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique Yelp user identifier
display_name	VARCHAR(100)	User's public Yelp display name

reviews

Column	Type	Description
review_idPK	INTEGER	Unique review identifier
user_id	INTEGER	Yelp user who authored the review
review_date	DATE	Date the review was published
review_text	TEXT	Review content

Your question is Running Total Reviews With Window Functions. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem