Top Categories With Window Functions | Dataford Interview Questions

The LexisNexis legal research platform needs a monthly view of which content categories attract the most reader activity. Write a PostgreSQL query that identifies the highest-performing content categories for each month in Q1 2025.

Requirements

Join content events to content items and include only view events from January through March 2025.
Aggregate total views and average engagement time by month and content category, excluding uncategorized content.
Use a window function to rank categories within each month and return every category tied for first place.

Schema

content_items

Column	Type	Description
content_idPK	INT	Unique content identifier
title	VARCHAR(200)	Content title
category	VARCHAR(80)	Content category

content_events

Column	Type	Description
event_idPK	INT	Unique event identifier
content_id	INT	Referenced content item identifier
event_date	DATE	Date of user activity
event_type	VARCHAR(20)	Type of user activity
engagement_seconds	INT	Seconds spent during the event

Your question is Top Categories With Window Functions. 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