May 25, 2026·12 min read

How to calculate MAE in SQL

Sharpen SQL for your next interview
500+ SQL problems with worked solutions — joins, window functions, CTEs.
Join the waitlist

Contents:

What MAE actually measures

MAE (Mean Absolute Error) is the average of the absolute differences between your model's predictions and the actual values. The headline reads in the same units as the target — predict revenue in dollars, MAE is in dollars, and $8.40 means your typical miss is roughly eight bucks. That interpretability is why regression dashboards at Stripe, Uber, and DoorDash usually surface MAE next to RMSE.

The deeper reason MAE belongs in your SQL toolkit is outlier behaviour: every error contributes linearly. One row that misses by $1,000 adds the same to the average as a thousand rows that each miss by $1. RMSE squares the gap first, so a single tail row dominates. If a single bad prediction is catastrophic (fraud loss, ETA for a customer commit), use RMSE; if the cost of error is roughly linear (weekly demand), MAE is the cleaner objective.

Imagine a Monday Slack from a PM: "what's our forecast error this month, by segment, and is it drifting?" The honest answer is rarely one number. You need MAE next to bias, MAE per segment, MAE week-over-week, all in SQL so the dashboard updates itself. The rest of this post walks that stack.

The SQL formula

The math is trivial — the SQL is where teams trip. Assume a predictions table with one row per (entity, prediction_date), columns predicted_value and actual_value. The base recipe is AVG(ABS(predicted - actual)), but you almost never want it alone:

SELECT
    COUNT(*)                                                    AS predictions,
    AVG(ABS(predicted_value - actual_value))                    AS mae,
    PERCENTILE_CONT(0.5) WITHIN GROUP (
        ORDER BY ABS(predicted_value - actual_value)
    )                                                           AS median_abs_error,
    PERCENTILE_CONT(0.95) WITHIN GROUP (
        ORDER BY ABS(predicted_value - actual_value)
    )                                                           AS p95_abs_error,
    MIN(ABS(predicted_value - actual_value))                    AS min_abs_error,
    MAX(ABS(predicted_value - actual_value))                    AS max_abs_error
FROM predictions
WHERE prediction_date >= CURRENT_DATE - INTERVAL '30 days'
  AND actual_value IS NOT NULL;

Three details worth defending in a code review. First, the actual_value IS NOT NULL filter — pipelines write the prediction row immediately and backfill the actual when the event resolves, so unfiltered rows silently inflate the denominator. Second, the median sitting next to the mean — when the two diverge (MAE $42, median $6), you have a long-tail problem the headline hides. Third, p95_abs_error — the worst typical miss, which is what an SRE or risk team will act on.

Load-bearing rule: always report MAE alongside a percentile of the absolute error. A single mean is a one-eyed view of a skewed distribution, and regression errors are almost always skewed.

MAE vs RMSE in one query

The cleanest way to spot outliers is to compute both metrics in the same pass and look at the gap. For a Normal error distribution the ratio RMSE/MAE ≈ 1.253; the further above that ratio you go, the heavier the tail:

SELECT
    AVG(ABS(error))                          AS mae,
    SQRT(AVG(POWER(error, 2)))               AS rmse,
    SQRT(AVG(POWER(error, 2)))
        / NULLIF(AVG(ABS(error)), 0)         AS rmse_to_mae_ratio,
    COUNT(*)                                 AS n
FROM (
    SELECT predicted_value - actual_value AS error
    FROM predictions
    WHERE prediction_date >= CURRENT_DATE - INTERVAL '30 days'
      AND actual_value IS NOT NULL
) e;

Here is the cheat sheet most teams print out:

Property MAE RMSE When it matters
Penalty shape Linear Quadratic Cost-of-error is non-linear → RMSE
Outlier sensitivity Robust Sensitive Heavy-tail residuals → prefer MAE
Units Same as target Same as target Both readable in dollars / minutes
Optimal predictor Median of y Mean of y Use median baseline when reporting MAE
Differentiable at 0 No Yes Training: gradient methods prefer RMSE
Typical ratio (Normal) — ≈ 1.253 × MAE Ratio >> 1.3 → outliers in residuals

A ratio of 1.8 or higher is the cue to drop everything and look at the top-50 errors before you trust the headline.

Bias decomposition

MAE is magnitude only — it tells you nothing about direction. A model that over-predicts every row by $5 has the same MAE as one that flips between +$5 and −$5 randomly, but the first one is a calibration bug and the second is noise. Track the signed mean error next to MAE:

SELECT
    COUNT(*)                                          AS n,
    AVG(predicted_value - actual_value)               AS mean_error,   -- bias
    AVG(ABS(predicted_value - actual_value))          AS mae,
    STDDEV(predicted_value - actual_value)            AS error_std,
    CASE
        WHEN AVG(predicted_value - actual_value) >  0.05 * AVG(actual_value)
             THEN 'systematic OVER-prediction'
        WHEN AVG(predicted_value - actual_value) < -0.05 * AVG(actual_value)
             THEN 'systematic under-prediction'
        ELSE 'approximately unbiased'
    END                                               AS direction
FROM predictions
WHERE prediction_date >= CURRENT_DATE - INTERVAL '30 days'
  AND actual_value IS NOT NULL;

Read it as a 2×2: low bias + low MAE is well-calibrated; low bias + high MAE is honest noise (re-engineer features); high bias + low MAE is a steady offset (fix with an intercept shift); high bias + high MAE is broken — retrain. The 5% threshold is a default, not gospel; tighten to 1% for Snowflake-scale revenue forecasts, loosen to 10% for early-stage churn.

Per-segment and weighted MAE

Aggregate MAE almost always hides a worse story. The classic trap: overall MAE looks acceptable, but enterprise customers are off by 40% while SMB is off by 2%, and the model is silently subsidizing the segment that pays the bills. Always drill:

SELECT
    segment,
    COUNT(*)                                                            AS predictions,
    AVG(predicted_value - actual_value)                                 AS bias,
    AVG(ABS(predicted_value - actual_value))                            AS mae,
    AVG(actual_value)                                                   AS avg_actual,
    AVG(ABS(predicted_value - actual_value))
        / NULLIF(AVG(actual_value), 0)                                  AS mae_pct_of_mean
FROM predictions p
JOIN entities  e USING (entity_id)
WHERE prediction_date >= CURRENT_DATE - INTERVAL '30 days'
  AND actual_value IS NOT NULL
GROUP BY segment
HAVING COUNT(*) >= 50
ORDER BY mae_pct_of_mean DESC;

The mae_pct_of_mean column is the magic: a $50 MAE on $5,000 invoices is great; the same $50 MAE on $80 invoices is a fire. The HAVING COUNT(*) >= 50 floor stops tiny segments from looking dramatic on three rows of noise.

For weighted MAE — when some predictions matter more than others — use a simple weighted sum:

SELECT
    SUM(weight * ABS(predicted_value - actual_value)) / NULLIF(SUM(weight), 0) AS weighted_mae
FROM predictions;

A common pattern at Amazon and DoorDash is weight = actual_value for revenue forecasts (large transactions matter more) or weight = 1 / variance when confidence varies. Document the weighting in a header comment — six months later, nobody remembers why the headline differs from AVG(ABS(...)).

Sharpen SQL for your next interview
500+ SQL problems with worked solutions — joins, window functions, CTEs.
Join the waitlist

MAE drift over time

A static MAE is a snapshot; a moving MAE is a control chart. Bucket by week (or day for high-volume systems) and watch the trend:

SELECT
    DATE_TRUNC('week', prediction_date)                          AS week,
    COUNT(*)                                                     AS n,
    AVG(ABS(predicted_value - actual_value))                     AS mae,
    AVG(predicted_value - actual_value)                          AS bias,
    PERCENTILE_CONT(0.95) WITHIN GROUP (
        ORDER BY ABS(predicted_value - actual_value)
    )                                                            AS p95_abs_error
FROM predictions
WHERE prediction_date >= CURRENT_DATE - INTERVAL '12 weeks'
  AND actual_value IS NOT NULL
GROUP BY 1
ORDER BY 1;

A 12-week window is the sweet spot for weekly business cycles — long enough to see seasonality, short enough to catch a regression from last sprint. The retrain trigger lives in the second derivative: three consecutive weeks of MAE above the 4-week trailing mean by more than 20% signals a data shift. Live this check in a dbt test or Great Expectations suite, not a human's head.

Common pitfalls

The first and most expensive mistake is treating MAE as comparable across targets of different scales. An MAE of $5 is excellent for a $500 order and catastrophic for a $4 latte. Always report mae_pct_of_mean (or WAPE) alongside the raw number when comparing models trained on different distributions. Engineers who skip this ship a model because "MAE dropped from 12 to 9" when the average target also dropped and relative error got worse.

A second trap is forgetting that MAE collapses direction. Two models with identical MAE can be wildly different products — one over-predicts demand and you over-stock, the other under-predicts and you stock out. The fix is the bias decomposition above; the cultural fix is training PMs to read MAE and bias as a pair. If your weekly review slide has one number on it, you are flying blind in one dimension.

The third trap is silent outlier inflation. MAE is robust relative to RMSE but not immune — one row with actual_value = 0 and predicted_value = 1,000,000 (join bug, currency mixup, stale cache) moves the average by the full delta over n. Quarantine errors above a hard threshold (ABS(error) > 10 * AVG(actual)) and re-run the headline without them. If it barely moves, you're clean; if it halves, you found a data bug.

The fourth pitfall is comparing MAE across different time windows. Jan–Mar vs Apr–Jun is fine only if the distribution is stationary, and for subscriptions, marketplaces, or demand, it is not. Fix a holdout window and benchmark every candidate on the same rows; teams that skip this celebrate MAE drops that are really seasonality.

The fifth pitfall is letting NULL actual_value rows pollute the average. Prediction tables are written-ahead — the row exists at prediction time, actual_value is NULL until resolution. SQL engines silently exclude NULLs from AVG, but COUNT(*) includes them, so mae and predictions desync: "10,000 predictions, MAE $4" is really "10,000 predictions, 3,200 evaluated, MAE $4 on those 3,200." Use COUNT(actual_value) or filter explicitly.

Optimization tips

For tables in the 10M–1B row range, three patterns earn their keep. Partition predictions by prediction_date so the 30-day window prunes partitions — on BigQuery and Snowflake this turns multi-minute queries into seconds. Pre-compute error and abs_error as generated columns (Postgres 12+, Snowflake virtual columns, BigQuery GENERATED ALWAYS AS) — a ~20% speedup on the dashboards that hit this metric most. Materialize per-segment per-week MAE into a metrics_mae_weekly rollup; ad-hoc queries read from the rollup, not raw.

On ClickHouse, quantileExact(0.95) is ~10× faster than PERCENTILE_CONT(0.95) WITHIN GROUP for typical residuals, with plenty of precision for monitoring. On BigQuery, swap PERCENTILE_CONT for APPROX_QUANTILES(abs_error, 100)[OFFSET(95)]. Document the dialect in a comment so the next engineer doesn't "fix" it back to standard.

If you want to drill SQL questions like this every day, NAILDD is launching with 500+ SQL problems built around exactly this pattern.

FAQ

What's the practical difference between MAE and RMSE?

Both report in target units, but RMSE squares errors first, so it punishes a few large misses much harder than many small ones. Use MAE when business cost is roughly linear (weekly demand, routine ETAs); use RMSE when a single big miss is catastrophic (fraud loss, capacity planning). Quick diagnostic: if RMSE/MAE is above 1.5, residuals have heavy tails and the choice of metric will materially change which model "wins."

What units is MAE expressed in?

The same units as your target. Predict price in USD, MAE is in USD; predict latency in ms, MAE is in ms. This is why MAE travels well in product reviews — a non-technical PM reads "average miss is $8.40" without translation. The flip side: you cannot compare MAE across models with different targets — that's what MAPE and percentage-of-mean wrappers are for.

How is MAE different from MAPE?

MAE averages absolute errors in absolute units; MAPE divides each absolute error by the actual value and averages the percentages. MAPE is scale-invariant (useful across products with different price points) but blows up when actual_value is near zero and is biased toward under-predictions. The common compromise is WAPE — sum of absolute errors over sum of actuals — which avoids divide-by-zero and is what most demand-planning teams ship.

Can I use MAE for probability predictions?

Technically yes — AVG(ABS(predicted_prob - actual_label)) is defined for actual_label ∈ {0, 1} — but it is rarely the right tool. For classification, log loss (penalizes confident wrong predictions) and Brier score (the proper scoring rule for probabilities) give you sharper diagnostics and align with the loss most classifiers were trained on. Reach for MAE on probabilities only as a quick sanity check, not as the headline metric.

My MAE is exactly zero — what's wrong?

Almost certainly leakage. Real-world MAE is never exactly zero on a non-trivial holdout. The two common causes: (1) actual_value was used as a feature during training, so the model regurgitates it; (2) the prediction was written after the actual was known, so you're scoring the model against itself. Check timestamps of predicted_at vs actual_observed_at and verify no feature is a function of the target.

How many rows do I need before MAE is trustworthy?

For a tight 95% confidence band, n ≥ 1,000 in the evaluation window, with ≥ 50 rows per segment when drilling. Below that, the metric is dominated by noise — two evaluations of the same model can differ by 10–20% from sampling alone. For small slices (a new segment with 30 rows), pair MAE with a bootstrap 95% CI so readers see uncertainty rather than a misleading point estimate.

Related reading