Churn Prediction in SQL: Rank Customers for Retention

Objective

Retention teams do not need a model. They need a list: which twenty accounts to call this week. Everything between your customer table and that list — the export, the notebook, the scoring job, the table you write the scores back to — is plumbing you have to own and keep in sync.

This recipe trains a churn model where the customers already are, then produces the ranked list as an ordinary SELECT your dashboard can read.

Step 1: A customer table with the usual signals

Tenure, recent engagement, and support friction — the three features almost every real churn model starts from.

-- Drop first so the recipe is re-runnable: CREATE TABLE IF NOT EXISTS leaves an
-- existing table in place and the INSERT below then fails on duplicate keys.
DROP TABLE IF EXISTS recipe_churn_customers;

CREATE TABLE recipe_churn_customers (
  customer_id       INT PRIMARY KEY,
  tenure_months     INT,
  logins_last_30d   INT,
  support_tickets   INT,
  churned           INT     -- 1 = left in the last quarter, 0 = still a customer
);

INSERT INTO recipe_churn_customers
  (customer_id, tenure_months, logins_last_30d, support_tickets, churned) VALUES
  (1,8,14,5,0), (2,15,27,1,0), (3,22,10,6,1), (4,29,23,2,0),
  (5,36,6,7,1), (6,7,19,3,0), (7,14,2,8,1), (8,21,15,4,0),
  (9,28,28,0,0), (10,35,11,5,0), (11,6,24,1,0), (12,13,7,6,1),
  (13,20,20,2,0), (14,27,3,7,1), (15,34,16,3,0), (16,5,29,8,1),
  (17,12,12,4,0), (18,19,25,0,0), (19,26,8,5,1), (20,33,21,1,0),
  (21,4,4,6,1), (22,11,17,2,0), (23,18,30,7,1), (24,25,13,3,0),
  (25,32,26,8,1), (26,3,9,4,1), (27,10,22,0,0), (28,17,5,5,1),
  (29,24,18,1,0), (30,31,1,6,1), (31,2,14,2,0), (32,9,27,7,1),
  (33,16,10,3,0), (34,23,23,8,1), (35,30,6,4,1), (36,1,19,0,0),
  (37,8,2,5,1), (38,15,15,1,0), (39,22,28,6,0), (40,29,11,2,0),
  (41,36,24,7,1), (42,7,7,3,0), (43,14,20,8,1), (44,21,3,4,1),
  (45,28,16,0,0), (46,35,29,5,0), (47,6,12,1,0), (48,13,25,6,0),
  (49,20,8,2,0), (50,27,21,7,1), (51,34,4,3,1), (52,5,17,8,1),
  (53,12,30,4,0), (54,19,13,0,0), (55,26,26,5,0), (56,33,9,1,0),
  (57,4,22,6,1), (58,11,5,2,0), (59,18,18,7,1), (60,25,1,3,1),
  (61,32,14,8,1), (62,3,27,4,1), (63,10,10,0,0), (64,17,23,5,0),
  (65,24,6,1,0), (66,31,19,6,0), (67,2,2,2,1), (68,9,15,7,1),
  (69,16,28,3,0), (70,23,11,8,1), (71,30,24,4,0), (72,1,7,0,1),
  (73,8,20,5,0), (74,15,3,1,0), (75,22,16,6,0), (76,29,29,2,0),
  (77,36,12,7,1), (78,7,25,3,0), (79,14,8,8,1), (80,21,21,4,0),
  (81,28,4,0,0), (82,35,17,5,0), (83,6,30,1,0), (84,13,13,6,0),
  (85,20,26,2,0), (86,27,9,7,1), (87,34,22,3,0), (88,5,5,8,1),
  (89,12,18,4,0), (90,19,1,0,0), (91,26,14,5,0), (92,33,27,1,0),
  (93,4,10,6,1), (94,11,23,2,0), (95,18,6,7,1), (96,25,19,3,0),
  (97,32,2,8,1), (98,3,15,4,1), (99,10,28,0,0), (100,17,11,5,0),
  (101,24,24,1,0), (102,31,7,6,1), (103,2,20,2,0), (104,9,3,7,1),
  (105,16,16,3,0), (106,23,29,8,1), (107,30,12,4,0), (108,1,25,0,0),
  (109,8,8,5,1), (110,15,21,1,0), (111,22,4,6,1), (112,29,17,2,0),
  (113,36,30,7,1), (114,7,13,3,0), (115,14,26,8,1), (116,21,9,4,0),
  (117,28,22,0,0), (118,35,5,5,1), (119,6,18,1,0), (120,13,1,6,1);

Step 2: Ground truth — check the balance before you train

SELECT
  churned,
  COUNT(*)                                   AS customers,
  ROUND(AVG(logins_last_30d), 1)             AS avg_logins,
  ROUND(AVG(support_tickets), 1)             AS avg_tickets,
  ROUND(AVG(tenure_months), 1)               AS avg_tenure
FROM recipe_churn_customers
GROUP BY churned
ORDER BY churned;

Expected: 71 retained and 49 churned, and the churned group shows visibly fewer logins and more tickets. Two things to take from this.

The split is 59/41, so accuracy is the wrong metric. A model that predicts "nobody churns" scores 59% accuracy and is worthless. That is why Step 3 asks for f1.

If the groups look identical here, stop. No algorithm recovers signal that is not in the features, and finding that out now costs one query instead of an afternoon.

Step 3: Train

CREATE EXPERIMENT recipe_churn_exp AS
  SELECT tenure_months, logins_last_30d, support_tickets, churned
  FROM recipe_churn_customers
WITH (
  task_type           = 'classification',
  target_column       = 'churned',
  algorithms          = ['xgboost','random_forest','logistic_regression'],
  optimization_metric = 'f1',
  validation_strategy = 'kfold',
  n_folds             = 3,
  max_trials          = 12,
  random_seed         = 42
);

optimization_metric = 'f1' balances catching churners against crying wolf. Optimise accuracy on imbalanced data and you get a model that is right most of the time and useless every time it matters.

random_seed = 42 makes the run reproducible — the same data gives the same model, which is what lets you tell a real improvement from search noise.

Step 4: Read the result

CREATE EXPERIMENT returns its result; there is no system table to query afterwards.

{
  "status": "success",
  "best_score": 0.969,
  "score_basis": "cross_validation",
  "total_trials": 12,
  "trials_attempted": 12,
  "failed_trials": [],
  "optimization_metric": "f1",
  "ignored_options": []
}

• best_score 0.969 is the F1 across 3 folds. Classification scores are plain metrics — unlike forecasting, there is no minus sign.
• score_basis: cross_validation confirms k-fold was applied. If it says holdout, your validation_strategy was not honoured.
• total_trials equals trials_attempted — nothing failed. When they differ, failed_trials names the algorithm and the reason.

An F1 of 0.97 on a clean synthetic set is expected. On real customer data, 0.7 is a good model; treat anything above 0.95 on production data as a prompt to go looking for leakage — a feature that encodes the answer, such as a cancellation date.

Step 5: Score two known-shape customers

DEPLOY MODEL recipe_churn_model FROM EXPERIMENT recipe_churn_exp;

PREDICT churn_risk USING recipe_churn_model AS
  SELECT 2  AS tenure_months, 3  AS logins_last_30d, 8 AS support_tickets
  UNION ALL
  SELECT 30 AS tenure_months, 28 AS logins_last_30d, 0 AS support_tickets;

Expected: the new, disengaged, ticket-heavy account scores about 0.95 and the long-tenured, active, quiet one about 0.06.

The result carries every input column plus the prediction — here four columns, (tenure_months, logins_last_30d, support_tickets, churn_risk). Read the last one. Taking the first column hands you back your own input.

Step 6: The thing retention actually asked for

PREDICT churn_risk USING recipe_churn_model AS
  SELECT tenure_months, logins_last_30d, support_tickets
  FROM recipe_churn_customers
  WHERE churned = 0;

Expected: one row per current customer with a risk score. Those are the accounts still with you, ranked by how likely they are not to be — which is the list the team wanted. Sort it, cut the top twenty, and that is the call sheet.

Scoring the churned = 0 rows is the point: predicting churn for customers who have already left is a reporting exercise, not a retention one.

Cleanup (Optional)

DROP TABLE IF EXISTS recipe_churn_customers;

Use it from your agent

• REST/SDK: POST /v1/query/execute with the CREATE EXPERIMENT text, read the returned JSON, then run the Step 6 PREDICT on a schedule.
• Keep it current: add schedule = '0 2 * * 1' to retrain every Monday as new churn outcomes land, or trigger on drift so it retrains only when the customer mix actually moves.
• Why in-DB: the scores are produced beside the customers, so there is no export, no scoring service, and no risk of the dashboard reading a stale copy.

Key Concepts Learned

• Check the class balance before choosing a metric. At 59/41, accuracy rewards a model that predicts nothing. F1 does not.
• Compare the groups before you train. If churned and retained customers look the same in your features, no algorithm will help.
• The prediction is the last column. Results carry your inputs alongside the output so you can join them; read the right one.
• Score the customers you can still keep. Filtering to churned = 0 is what turns a model into a retention list.
• A suspiciously high score on real data means leakage, not success.