Dplyr Cohort Aggregation | Dataford Interview Questions

Given this raw dataset of user interactions, write a dplyr pipeline to aggregate the average session length by user cohort at Betterup.

For this PostgreSQL exercise, write the equivalent SQL query using the provided users and user_interactions tables. Include cohorts without recorded interactions, and ignore missing session lengths when calculating averages.

Output

One row per user cohort, including cohorts without interactions
Columns: cohort, average_session_length
Round the average to two decimal places and sort cohorts ascending, with NULL cohorts last

Schema

users

Column	Type	Description
user_idPK	INT	Unique BetterUp user identifier
cohort	VARCHAR(7)	User cohort represented as a YYYY-MM value

user_interactions

Column	Type	Description
interaction_idPK	INT	Unique interaction identifier
user_id	INT	User associated with the interaction
session_length_minutes	INT	Session duration in minutes

Your question is Dplyr Cohort 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