Star Schema for Ad Metrics | Dataford Interview Questions

How would you design a star schema for tracking user ad impressions and clicks across different platforms for Reddit?

Assume the schema includes user and platform dimensions plus a daily ad engagement fact table. Write a SQL query that summarizes all platforms, including platforms without activity.

Output

One row per platform with platform_name, unique_users, total_impressions, total_clicks, and ctr_percent
Include platforms with no fact rows, representing their metrics as zero
Sort by total_impressions descending, then platform_id ascending

Schema

dim_platforms

Column	Type	Description
platform_idPK	INT	Surrogate key for an advertising platform
platform_name	VARCHAR(50)	Name of the Reddit ad delivery platform
platform_type	VARCHAR(30)	Platform classification

dim_users

Column	Type	Description
user_idPK	INT	Reddit user identifier
user_name	VARCHAR(80)	Anonymized Reddit username
country_code	VARCHAR(2)	Two-letter user country code

fact_ad_engagement_daily

Column	Type	Description
engagement_idPK	INT	Fact row identifier
user_id	INT	User associated with the daily engagement
platform_id	INT	Platform associated with the engagement
engagement_date	DATE	Date represented by the fact row
impressions	INT	Number of ad impressions
clicks	INT	Number of ad clicks

Your question is Star Schema for Ad Metrics. 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