Join and Time-Interval Aggregation | Dataford Interview Questions

Write a SQL query to join these tables and aggregate user behavior by time interval.

Use the provided user and behavior event data. Summarize activity in hourly intervals, retain users with no matching events, and treat missing duration values as zero.

Output

One row per user and hourly interval, plus one row for users with no events
Columns: user_id, user_name, time_interval, behavior_count, total_duration_seconds, and distinct_event_types
Order by user_id, then time_interval ascending, with missing intervals last

Schema

users

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

behavior_events

Column	Type	Description
event_idPK	INT	Unique behavior event identifier
user_id	INT	User associated with the event
occurred_at	TIMESTAMP	Timestamp when the behavior occurred
event_type	VARCHAR(50)	Type of behavior event
duration_seconds	INT	Duration associated with the event in seconds

Your question is Join and Time-Interval Aggregation. 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