Rolling Average Sign-Ups Query | Dataford Interview Questions

Your question is Rolling Average Sign-Ups Query. Start with the requirements and the two tables on the right.

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

Problem

WeWork wants to monitor member acquisition trends across active New York locations. Write a PostgreSQL query that reports the 30-day rolling average of daily member sign-ups for the period ending February 28, 2025.

Requirements

Include active WeWork locations in New York only.
Count sign-ups by location and calendar date, treating dates without sign-ups as zero.
Calculate a rolling average across the current date and the preceding 29 calendar days.
Return only dates with at least one sign-up, ordered by location and date.

Schema

member_signups

Column	Type	Description
signup_idPK	INT	Unique sign-up event identifier
member_id	INT	Member identifier
location_id	INT	WeWork location associated with the sign-up
signup_date	DATE	Date of the member sign-up

wework_locations

Column	Type	Description
location_idPK	INT	Unique WeWork location identifier
location_name	VARCHAR(100)	Display name of the location
city	VARCHAR(80)	City containing the location
is_active	BOOLEAN	Whether the location is active