Running Total of Daily Signups | Dataford Interview Questions

DeFiner wants a reliable daily signup trend for its analytics dashboard. Write a PostgreSQL query that reports daily signups during February 1 through February 10, 2025, including days with no qualifying signups.

Requirements

Count distinct users whose signup event occurred on each calendar day.
Exclude test users, deleted users, users without a recognized acquisition channel, duplicate signup events, and events outside the requested date range.
Calculate the previous day's signup count with LAG.
Calculate the cumulative signup total with a windowed SUM ordered by date.
Return one row per calendar day in ascending date order.

Schema

users

Column	Type	Description
user_idPK	INT	Unique DeFiner user identifier
created_at	TIMESTAMP	Account creation timestamp
channel_id	INT	Acquisition channel identifier
is_test	BOOLEAN	Whether the account is a test account
deleted_at	TIMESTAMP	Account deletion timestamp

signup_events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User associated with the event
event_type	VARCHAR(30)	Tracked event name
occurred_at	TIMESTAMP	Timestamp when the event occurred

acquisition_channels

Column	Type	Description
channel_idPK	INT	Unique acquisition channel identifier
channel_name	VARCHAR(100)	Human-readable acquisition channel
is_active	BOOLEAN	Whether the channel is currently active

Your question is Running Total of Daily Signups. 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