Power Users Leaving Reviews in Yelp Categories | Dataford Interview Questions

Yelp wants to identify power users who consistently review different types of businesses. Write a PostgreSQL query that finds users with at least two distinct business categories reviewed in each of two consecutive calendar months.

Requirements

Consider only reviews with status = 'published' and businesses with a non-null category.
Aggregate reviews by user and calendar month, counting reviews and distinct categories.
Use a window function to identify consecutive months for the same user.
Return pairs where both months contain at least two distinct categories and at least four published reviews combined.

Schema

users

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

businesses

Column	Type	Description
business_idPK	INT	Unique business identifier
business_name	VARCHAR(150)	Yelp business name
category	VARCHAR(80)	Primary business category

reviews

Column	Type	Description
review_idPK	INT	Unique review identifier
user_id	INT	Reviewing user
business_id	INT	Reviewed business
reviewed_at	DATE	Review date
status	VARCHAR(20)	Review state

Your question is Power Users Leaving Reviews in Yelp Categories. Start with the requirements and the three 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