XGBoost-Style Regression in SQL: Estimate Property Prices

Objective

Predicting a number from a few columns — price, duration, spend, load — is the most common modelling job there is, and the one most often shipped as a Python service sitting next to the database that holds the data.

This recipe fits gradient-boosted trees, a random forest and a linear baseline in one statement, keeps whichever generalises best, and then scores new rows as ordinary SQL. The linear model is in the list on purpose: it is the thing boosting has to beat before the extra complexity is worth owning.

Step 1: A valuation table

-- Drop first so the recipe is re-runnable.
DROP TABLE IF EXISTS recipe_reg_properties;

CREATE TABLE recipe_reg_properties (
  property_id   INT PRIMARY KEY,
  sqm           INT,
  bedrooms      INT,
  age_years     INT,
  km_to_centre  INT,
  price         DOUBLE
);

INSERT INTO recipe_reg_properties
  (property_id, sqm, bedrooms, age_years, km_to_centre, price) VALUES
  (1,52,2,11,4,72537), (2,59,3,22,7,78574), (3,66,4,33,10,84611),
  (4,73,1,44,13,30648), (5,80,2,55,16,36685), (6,87,3,6,19,96722),
  (7,94,4,17,22,102759), (8,101,1,28,25,48796), (9,108,2,39,3,117333),
  (10,115,3,50,6,123370), (11,122,4,1,9,183407), (12,129,1,12,12,129444),
  (13,136,2,23,15,135481), (14,143,3,34,18,141518), (15,150,4,45,21,147555),
  (16,157,1,56,24,93592), (17,164,2,7,2,216129), (18,51,3,18,5,78166),
  (19,58,4,29,8,84203), (20,65,1,40,11,30240), (21,72,2,51,14,36277),
  (22,79,3,2,17,96314), (23,86,4,13,20,102351), (24,93,1,24,23,48388),
  (25,100,2,35,1,116925), (26,107,3,46,4,122962), (27,114,4,57,7,128999),
  (28,121,1,8,10,129036), (29,128,2,19,13,135073), (30,135,3,30,16,141110),
  (31,142,4,41,19,147147), (32,149,1,52,22,93184), (33,156,2,3,25,153221),
  (34,163,3,14,3,221758), (35,50,4,25,6,83795), (36,57,1,36,9,29832),
  (37,64,2,47,12,35869), (38,71,3,58,15,41906), (39,78,4,9,18,101943),
  (40,85,1,20,21,47980), (41,92,2,31,24,54017), (42,99,3,42,2,122554),
  (43,106,4,53,5,128591), (44,113,1,4,8,128628), (45,120,2,15,11,134665),
  (46,127,3,26,14,140702), (47,134,4,37,17,146739), (48,141,1,48,20,92776),
  (49,148,2,59,23,98813), (50,155,3,10,1,221350), (51,162,4,21,4,227387),
  (52,49,1,32,7,29424), (53,56,2,43,10,35461), (54,63,3,54,13,41498),
  (55,70,4,5,16,101535), (56,77,1,16,19,47572), (57,84,2,27,22,53609),
  (58,91,3,38,25,59646), (59,98,4,49,3,128183), (60,105,1,0,6,128220),
  (61,112,2,11,9,134257), (62,119,3,22,12,140294), (63,126,4,33,15,146331),
  (64,133,1,44,18,92368), (65,140,2,55,21,98405), (66,147,3,6,24,158442),
  (67,154,4,17,2,226979), (68,161,1,28,5,173016), (69,48,2,39,8,35053),
  (70,55,3,50,11,41090), (71,62,4,1,14,101127), (72,69,1,12,17,47164),
  (73,76,2,23,20,53201), (74,83,3,34,23,59238), (75,90,4,45,1,127775),
  (76,97,1,56,4,73812), (77,104,2,7,7,133849), (78,111,3,18,10,139886),
  (79,118,4,29,13,145923), (80,125,1,40,16,91960), (81,132,2,51,19,97997),
  (82,139,3,2,22,158034), (83,146,4,13,25,164071), (84,153,1,24,3,172608),
  (85,160,2,35,6,178645), (86,47,3,46,9,40682), (87,54,4,57,12,46719),
  (88,61,1,8,15,46756), (89,68,2,19,18,52793), (90,75,3,30,21,58830),
  (91,82,4,41,24,64867), (92,89,1,52,2,73404), (93,96,2,3,5,133441),
  (94,103,3,14,8,139478), (95,110,4,25,11,145515), (96,117,1,36,14,91552),
  (97,124,2,47,17,97589), (98,131,3,58,20,103626), (99,138,4,9,23,163663),
  (100,145,1,20,1,172200), (101,152,2,31,4,178237), (102,159,3,42,7,184274),
  (103,46,4,53,10,46311), (104,53,1,4,13,46348), (105,60,2,15,16,52385),
  (106,67,3,26,19,58422), (107,74,4,37,22,64459), (108,81,1,48,25,10496),
  (109,88,2,59,3,79033), (110,95,3,10,6,139070), (111,102,4,21,9,145107),
  (112,109,1,32,12,91144), (113,116,2,43,15,97181), (114,123,3,54,18,103218),
  (115,130,4,5,21,163255), (116,137,1,16,24,109292), (117,144,2,27,2,177829),
  (118,151,3,38,5,183866), (119,158,4,49,8,189903), (120,45,1,0,11,45940),
  (121,52,2,11,14,51977), (122,59,3,22,17,58014), (123,66,4,33,20,64051),
  (124,73,1,44,23,10088), (125,80,2,55,1,78625), (126,87,3,6,4,138662),
  (127,94,4,17,7,144699), (128,101,1,28,10,90736), (129,108,2,39,13,96773),
  (130,115,3,50,16,102810), (131,122,4,1,19,162847), (132,129,1,12,22,108884),
  (133,136,2,23,25,114921), (134,143,3,34,3,183458), (135,150,4,45,6,189495),
  (136,157,1,56,9,135532), (137,164,2,7,12,195569), (138,51,3,18,15,57606),
  (139,58,4,29,18,63643), (140,65,1,40,21,9680), (141,72,2,51,24,15717),
  (142,79,3,2,2,138254), (143,86,4,13,5,144291), (144,93,1,24,8,90328),
  (145,100,2,35,11,96365), (146,107,3,46,14,102402), (147,114,4,57,17,108439),
  (148,121,1,8,20,108476), (149,128,2,19,23,114513), (150,135,3,30,1,183050);

Step 2: Ground truth — know the scale of what you are predicting

An error of "1,700" means nothing until you know whether prices are in the thousands or the millions.

SELECT
  COUNT(*)                   AS properties,
  ROUND(AVG(price), 0)       AS avg_price,
  ROUND(MIN(price), 0)       AS min_price,
  ROUND(MAX(price), 0)       AS max_price
FROM recipe_reg_properties;

Expected: 150 properties averaging 106,104, ranging 9,680 to 227,387. Keep the average in mind — it is the denominator for every error figure that follows.

Step 3: Train three model families at once

CREATE EXPERIMENT recipe_reg_exp AS
  SELECT sqm, bedrooms, age_years, km_to_centre, price
  FROM recipe_reg_properties
WITH (
  task_type           = 'regression',
  target_column       = 'price',
  algorithms          = ['xgboost','gradient_boosting','linear_regression'],
  optimization_metric = 'rmse',
  validation_strategy = 'kfold',
  n_folds             = 3,
  max_trials          = 15,
  random_seed         = 42
);

xgboost here is a native implementation in the spirit of the library of that name — gradient-boosted trees trained in-process. It is not the upstream package embedded, and it does not read or write upstream model files. Use the family name with that qualifier.

Step 4: Read the score — and mind the sign

{
  "status": "success",
  "best_score": -1674.7,
  "score_basis": "cross_validation",
  "total_trials": 13,
  "trials_attempted": 13,
  "failed_trials": [],
  "optimization_metric": "rmse",
  "ignored_options": []
}

best_score is negative because rmse is a loss. -1674.7 is a root mean squared error of 1,674.7 — against an average price of 106,104, that is about 1.6%. Closer to zero is better.

This is where the sign convention bites: a loss metric (rmse, mae, mse) comes back negated, while a score metric (f1, accuracy, auc, r2) comes back as itself and higher is better. The same best_score field means opposite things depending on the metric you asked for, so read optimization_metric alongside it.

Note total_trials is 13 against trials_attempted 13 but max_trials was 15: the search stopped early because the space was exhausted, not because anything failed — failed_trials is empty.

Step 5: Score a property

DEPLOY MODEL recipe_reg_model FROM EXPERIMENT recipe_reg_exp;

PREDICT estimated_price USING recipe_reg_model AS
  SELECT 90 AS sqm, 3 AS bedrooms, 10 AS age_years, 5 AS km_to_centre;

Expected: about 134,000. The underlying relationship values this property at roughly 131,500 before the per-row variation the data carries, so a model landing within a couple of percent of that has learned the structure rather than memorised rows.

The result carries every input column plus the prediction — five columns here. Read the last one.

Step 6: Find the properties the model disagrees with

The most useful output of a valuation model is not the prediction. It is the residual: where the asking price and the model part company.

PREDICT estimated_price USING recipe_reg_model AS
  SELECT sqm, bedrooms, age_years, km_to_centre
  FROM recipe_reg_properties
  WHERE km_to_centre <= 5;

Expected: one row per central property with its estimate. Join those back to price in your application and the largest gaps are your shortlist — either genuinely mispriced, or carrying something the four features cannot see.

Cleanup (Optional)

DROP TABLE IF EXISTS recipe_reg_properties;

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 whenever you need estimates.
• Keep it honest: add schedule to retrain as new sales land, or trigger on drift so it retrains when the mix of properties moves rather than on a clock.
• Why in-DB: no export, no model server, and the estimate is produced in the same query that reads the row it describes.

Key Concepts Learned

• Loss metrics come back negated; score metrics do not. rmse gives -1674.7; f1 would give 0.97. Read optimization_metric with the score.
• Express error as a percentage of the mean. 1,674 is meaningless alone and clearly good against an average of 106,104.
• Always include a linear baseline. If boosting cannot beat it, the simpler model is the one to ship.
• xgboost here is a native implementation in that family, not the upstream library, and there is no model-file interoperability.
• The residual is the product. Predictions are interesting; disagreements between prediction and reality are actionable.