Count Distinct Users by Day | Dataford Interview Questions

Shopify Analytics needs a daily activity metric showing how many unique users interacted with Shopify Admin. Write a PostgreSQL query using the user_events table.

Requirements

Group events by calendar day, derived from event_timestamp.
Count each user_id at most once per day, excluding events without a timestamp or user ID.
Return results in ascending date order with the columns activity_day and distinct_users.

Schema

user_events

Column	Type	Description
event_idPK	BIGINT	Unique event identifier
user_id	BIGINT	Shopify user who generated the event
event_timestamp	TIMESTAMP	Timestamp when the event occurred
event_name	VARCHAR(100)	Name of the recorded event

Your question is Count Distinct Users by Day. Start with the requirements and the one table 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

Shopify Analytics needs a daily activity metric showing how many unique users interacted with Shopify Admin. Write a PostgreSQL query using the user_events table.

Requirements

Group events by calendar day, derived from event_timestamp.
Count each user_id at most once per day, excluding events without a timestamp or user ID.
Return results in ascending date order with the columns activity_day and distinct_users.

Schema

user_events

Column	Type	Description
event_idPK	BIGINT	Unique event identifier
user_id	BIGINT	Shopify user who generated the event
event_timestamp	TIMESTAMP	Timestamp when the event occurred
event_name	VARCHAR(100)	Name of the recorded event

Your question is Count Distinct Users by Day. Start with the requirements and the one table 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.