Window Functions vs Joins | Dataford Interview Questions

Explain the difference between window functions and joins in terms of performance and use cases.

Use the supplied PostgreSQL tables to support the explanation with a query that combines user activity and produces a per-user ranking. Discuss why the selected approach preserves row detail and how an equivalent join-based approach could affect performance.

Output

One row per user, including users with no activity
Columns: team, user_name, activity_count, and activity_rank
Rank users within each team by activity count descending, with ties sharing a rank
Order by team ascending with null teams last, then rank and user ID

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
user_name	VARCHAR(100)	User display name
team	VARCHAR(100)	User team

activity_events

Column	Type	Description
event_idPK	INT	Unique activity event identifier
user_id	INT	User associated with the event
event_type	VARCHAR(50)	Type of activity event

Your question is Window Functions vs Joins. Start with the requirements and the two 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