Rank Users by Cohort Activity | Dataford Interview Questions

Your question is Rank Users by Cohort Activity. 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

Alpaca wants to compare user engagement among customers who joined during the same month. Write a PostgreSQL query that ranks users by their qualifying Alpaca app activity within each signup-month cohort.

Requirements

Include users who signed up during Q1 2025, including users with no qualifying activity.
Count each user's activity event from Q1 2025 and derive the cohort as the signup month in YYYY-MM format.
Use a window function to assign the same rank to users with tied activity counts.
Return results ordered by cohort month, activity count descending, and user ID ascending.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique Alpaca user identifier
display_name	VARCHAR(100)	User display name
signup_date	DATE	Date the user joined Alpaca

activity_events

Column	Type	Description
event_idPK	INTEGER	Unique activity event identifier
user_id	INTEGER	User associated with the event
event_type	VARCHAR(50)	Type of Alpaca app activity
occurred_at	TIMESTAMP	Timestamp when the activity occurred