SQL Top 10% by Engagement | Dataford Interview Questions

Your question is SQL Top 10% by Engagement. 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

The Facebook Growth team wants to identify users with the strongest engagement during January 2025. Write a PostgreSQL query that ranks users by their total Facebook app session duration and returns the top 10%.

Requirements

Include every registered user, including users with no qualifying sessions.
Sum session duration in seconds for sessions from 2025-01-01 through 2025-01-31; treat NULL durations as zero.
Use a window function to divide users into ten engagement buckets, with the highest-duration users in bucket 1.
Return the user ID, user name, total duration, and engagement bucket, ordered by duration descending and user ID ascending.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique Facebook user identifier
user_name	VARCHAR(100)	User display name
signup_date	DATE	Date the user registered

facebook_app_sessions

Column	Type	Description
session_idPK	INTEGER	Unique session identifier
user_id	INTEGER	User who generated the session
session_start	TIMESTAMP	Session start timestamp
duration_seconds	INTEGER	Session duration in seconds