SQL Query
SQL query to identify customers at risk of churn, written for PostgreSQL 15
No card required
What this brief specifies
Schema
Three tables are involved: customers, subscriptions with start and end dates and status, and usage_events logging individual event types and timestamps.
Scale
About 200,000 customers and 2 million usage events a month, with the query running daily inside the BI tool.
What counts as at risk
A paying, active customer flagged only if usage in the last 30 days declined more than 40% versus the prior 30 days.
What makes this hard
The definition of at risk here is relative, not absolute, a decline of more than 40% comparing two rolling 30-day windows per customer, computed against a usage_events table adding roughly 2 million rows every month. A query that returns correct results on a small sample but needs a full table scan or an expensive self-join to build those two windows per customer will either time out or slow the daily BI refresh once months of event history have piled up behind the roughly 200,000 customer base. Getting active status right matters too, subscriptions carries both a start and an end date alongside status, so the 40% comparison has to apply only to customers whose subscription genuinely spans both windows, not to someone who signed up 20 days ago and simply has less history, which would look like a decline without being one.
Related use cases
See how Flaibo works for Founder.
Frequently asked questions
The next early-warning query that has to compare two rolling windows per customer at real volume, and keep running inside a daily refresh rather than as a one-off analysis, benefits from a definition of at risk precise enough to write once, built against the actual scale from the start rather than tuned for accuracy on a small sample and left to slow down as the data grows.