Median and SQL on Reddit Data | Dataford Interview Questions

Your question is Median and SQL on Reddit Data. 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

Reddit analysts want to compare the typical post performance across active communities. Write a PostgreSQL query that calculates the median post score for each qualifying community during January 2025.

Requirements

Join active communities to their Reddit posts.
Consider only posts created from 2025-01-01 through 2025-01-31, inclusive, and ignore posts with a NULL score.
Return communities with at least three scored posts, along with the scored post count and continuous median score.
Order the result alphabetically by community name.

Use PostgreSQL's percentile_cont(0.5) WITHIN GROUP (ORDER BY ...) to calculate the median. The median should be computed separately for each community, not across all Reddit posts.

Schema

reddit_communities

Column	Type	Description
community_idPK	INT	Unique Reddit community identifier
community_name	VARCHAR(100)	Reddit community name
is_active	BOOLEAN	Whether the community is active

reddit_posts

Column	Type	Description
post_idPK	INT	Unique Reddit post identifier
community_id	INT	Referenced Reddit community
created_at	DATE	Date the post was created
score	INT	Net Reddit post score
title	TEXT	Post title