Top Churn Products by Region | Dataford Interview Questions

Your question is Top Churn Products by Region. Start with the requirements and the three 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

Deutsche Telekom wants to compare subscription churn across regional product portfolios, including MagentaMobil, MagentaTV, and broadband products. Write a PostgreSQL query to identify the top three products by churn rate within each region for subscriptions started during the first half of 2025.

Requirements

Join regions, products, and subscriptions using their key relationships.
Calculate each product's churn rate as churned eligible subscriptions divided by eligible subscriptions, expressed as a percentage. A subscription is churned when churned_at is not null.
Include products with no eligible subscriptions, treating their churn rate as 0.00 percent.
Rank products within each region by churn rate descending, then product ID ascending, and return only the top three products per region.

Schema

regions

Column	Type	Description
region_idPK	INT	Unique regional market identifier
region_name	VARCHAR(100)	Name of the regional market

products

Column	Type	Description
product_idPK	INT	Unique product identifier
region_id	INT	References the product's regional market
product_name	VARCHAR(150)	Deutsche Telekom product name

subscriptions

Column	Type	Description
subscription_idPK	INT	Unique subscription identifier
product_id	INT	References the subscribed product
start_date	DATE	Date on which the subscription started
churned_at	DATE	Date the subscription churned, or NULL if retained