flaibo
Data Structure

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.

FounderCorporateSmeFinanceConsultantMarketing

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.

Ready to try this in Flaibo?

Get started free

Your context everywhere