Cohort Conversion Before vs After

HardSQL · PostgreSQL

Problem

Santander wants to evaluate the impact of a campaign promoting the Santander Edge current account. Write a PostgreSQL query that compares each customer signup cohort's conversion rate during the 30 days before campaign exposure with the 30 days after exposure.

Requirements

Consider only participants in Santander Edge Spring 2024.
Define conversion as at least one approved application for the Santander Edge account in the relevant 30-day period.
Return one row per signup cohort with cohort size, before and after conversion counts, conversion rates, and the percentage-point lift after the campaign.
Use a left join for applications so customers without applications remain in the denominator. Order by cohort month.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique Santander customer identifier
customer_name	VARCHAR(100)	Customer name
signup_date	DATE	Date the customer signed up

campaign_participants

Column	Type	Description
customer_idPK	INT	Customer exposed to a campaign
campaign_name	VARCHAR(150)	Campaign name
campaign_start_date	DATE	Date used as the campaign exposure boundary
campaign_channel	VARCHAR(30)	Channel through which the campaign was delivered

applications

Column	Type	Description
application_idPK	INT	Unique product application identifier
customer_id	INT	Customer who submitted the application
application_date	DATE	Date the application was submitted
approved	BOOLEAN	Application approval status
product_name	VARCHAR(100)	Product requested in the application

Tablescustomerscampaign_participantsapplications