SQL for Good Users | Dataford Interview Questions

Your question is SQL for Good Users. Start with the requirements and the one table 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

Taboola wants to identify highly engaged users of Taboola Ads Manager. A Good User is a user whose average number of sessions per observed week is at least 3. Calculate the average session duration for each Good User.

Write a PostgreSQL query using the sessions table.

Requirements

Group sessions by userId and calendar week, where weeks begin on Monday.
Calculate each user's average weekly session count across weeks in which that user had at least one session.
Keep only users whose average weekly session count is at least 3.
Return each qualifying userId and their average session duration across all recorded sessions.
Ignore rows with a null userId or null data_timestamp. PostgreSQL's AVG should ignore null durations.
Sort the output by userId in ascending order.

Schema

sessions

Column	Type	Description
IdPK	INTEGER	Unique session identifier
userId	INTEGER	Taboola Ads Manager user identifier
duration	INTEGER	Session duration in seconds
data_timestamp	TIMESTAMP	Timestamp when the session was recorded

Your question is SQL for Good Users. Start with the requirements and the one table 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.

Write a PostgreSQL query using the sessions table.

Requirements

Group sessions by userId and calendar week, where weeks begin on Monday.
Calculate each user's average weekly session count across weeks in which that user had at least one session.
Keep only users whose average weekly session count is at least 3.
Return each qualifying userId and their average session duration across all recorded sessions.
Ignore rows with a null userId or null data_timestamp. PostgreSQL's AVG should ignore null durations.
Sort the output by userId in ascending order.

Schema

sessions

Column	Type	Description
IdPK	INTEGER	Unique session identifier
userId	INTEGER	Taboola Ads Manager user identifier
duration	INTEGER	Session duration in seconds
data_timestamp	TIMESTAMP	Timestamp when the session was recorded