Rolling Cohort Default Rate SQL

HardSQL · PostgreSQL

Problem

Forward Financing wants to monitor credit performance for its most recently funded merchant cohort. Write a PostgreSQL query that calculates the rolling 30-day default rate for that cohort.

Requirements

Define the most recent cohort as all merchants with at least one funding event during the month containing the latest funded_at value.
Use each cohort merchant's first default date only, ignoring later default events for the same merchant.
Return one row for each date on which a cohort merchant first defaulted.
Calculate the number of distinct first defaults in the current date and prior 29 calendar days, the cohort size, and the rolling default rate.
Sort results chronologically. The rate should be rounded to four decimal places.

Schema

merchants

Column	Type	Description
merchant_idPK	INTEGER	Unique merchant identifier
merchant_name	VARCHAR(120)	Merchant business name
segment	VARCHAR(40)	Merchant segment

funding_events

Column	Type	Description
funding_event_idPK	INTEGER	Unique funding event identifier
merchant_id	INTEGER	Merchant receiving funding
funded_at	TIMESTAMP	Timestamp when the funding event occurred
funded_amount	NUMERIC(12,2)	Amount funded

default_events

Column	Type	Description
default_event_idPK	INTEGER	Unique default event identifier
merchant_id	INTEGER	Merchant associated with the default
defaulted_at	TIMESTAMP	Timestamp when the default was recorded
default_reason	VARCHAR(80)	Reason recorded for the default

Tablesmerchantsfunding_eventsdefault_events