Event Duration — Solution & ExplanationProblems / Event Duration / EditorialApproach

Conditional aggregation: Use MIN(CASE WHEN event_type = 'start' THEN event_date END) to extract the start date and similarly for end.

Compute duration: DATEDIFF('day', start_date, end_date) returns the duration in days.

Group by session: Each session has exactly one start and one end, so grouping by session_id gives one row per session.

Alternative ApproachYou can also self-join the table—filter for start events and end events separately, then join on session_id. Conditional aggregation avoids the join overhead.Key ConceptConditional aggregation with CASE WHEN inside aggregate functions is a powerful technique for pivoting event-based data into a row-per-entity format.Hints

Use conditional aggregation: MIN(CASE WHEN event_type = 'start' THEN event_date END).

Alternatively, filter and self-join start and end events on session_id.

DATEDIFF('day', start_date, end_date) gives the session duration.

Solve Event Duration yourself →