SQL With Window Functions | Dataford Interview Questions

TikTok's creator analytics team wants to identify the strongest public videos for each active creator. Write a PostgreSQL query that calculates January engagement and ranks videos within each creator.

Requirements

Include only public videos published in January 2025 by active creators.
Calculate video engagement as likes plus comments plus shares from metrics recorded during January 2025. Treat NULL metrics and videos without metric rows as zero.
Use a window function to return the top two videos per creator, breaking engagement ties with the smaller video_id.

Schema

creators

Column	Type	Description
creator_idPK	INT	Unique creator identifier
handle	VARCHAR(50)	TikTok creator handle
creator_tier	VARCHAR(20)	Creator classification
is_active	BOOLEAN	Whether the creator is active

videos

Column	Type	Description
video_idPK	INT	Unique video identifier
creator_id	INT	Creator who owns the video
caption	VARCHAR(200)	Video caption
published_at	DATE	Date the video was published
visibility	VARCHAR(20)	Visibility status of the video

video_daily_metrics

Column	Type	Description
metric_idPK	INT	Unique metrics record identifier
video_id	INT	Video associated with the metrics
metric_date	DATE	Date of the daily metrics
likes	INT	Number of likes
comments	INT	Number of comments
shares	INT	Number of shares

Your question is SQL With Window Functions. Start with the requirements and the three 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