Cohort Conversion Comparison | Dataford Interview Questions

Business Context

Mirakl marketplace analytics needs to compare how effectively buyer cohorts convert after joining the marketplace.

Task

Write a PostgreSQL query that calculates and ranks buyer conversion rates by signup-month cohort for January through April 2025. A buyer is converted if they have at least one order with order_status = 'completed'.

Requirements

Derive each buyer's cohort from the month of signup_date using PostgreSQL date functions.
Include every eligible buyer in the cohort denominator, including buyers without orders or with only non-completed orders.
Return cohort size, converted buyers, conversion rate as a percentage rounded to two decimals, and the cohort rank using a window function.
Sort by conversion rate descending, then cohort month ascending.

Schema

marketplace_buyers

Column	Type	Description
buyer_idPK	INTEGER	Unique Mirakl marketplace buyer identifier
signup_date	DATE	Date the buyer joined the marketplace
acquisition_channel	VARCHAR(30)	Channel that generated the buyer signup

marketplace_orders

Column	Type	Description
order_idPK	INTEGER	Unique marketplace order identifier
buyer_id	INTEGER	Buyer associated with the order
order_date	DATE	Date the order was placed
order_status	VARCHAR(20)	Order lifecycle status

Your question is Cohort Conversion Comparison. 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