Average Session Time by User | Dataford Interview Questions

Your question is Average Session Time by User. 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

Nielsen Digital Content Ratings analysts need to understand how long each user spends consuming digital content. A session is defined as the elapsed time between page_load and page_exit for a page view.

Write a PostgreSQL query that calculates the average completed session duration for every user.

Requirements

Use a CTE to calculate each valid session duration in seconds.
Exclude rows with a missing page_exit or with page_exit earlier than page_load.
Return every user, including users with no valid completed sessions. Represent their average as NULL.
Round average duration to two decimal places and order by user_id ascending.

Schema

users

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

page_sessions

Column	Type	Description
session_idPK	INTEGER	Unique page-view identifier
user_id	INTEGER	References users.user_id
page_url	TEXT	Page visited
page_load	TIMESTAMP	Page load timestamp
page_exit	TIMESTAMP	Page exit timestamp