SQL Statistical Analysis — Applied Residual Analysis

QUESTION 6

Channel-level correlation analysis — Compare and rank correlation strength across segments with CORR + GROUP BY

CORRGROUP BYCorrelation AnalysisSegment ComparisonABS / CASE WHEN

Background ▼

CORR(Y, X) is an ordinary aggregate function, so it can be combined with GROUP BY to calculate a correlation coefficient for every group in one query. Unlike RANK and similar functions, it cannot be used as a window function.

-- Calculate correlation for every group with GROUP BY + CORR
SELECT channel,
  ROUND(CORR(revenue, ad_cost)::numeric, 4) AS corr_coef,
  REGR_COUNT(revenue, ad_cost)                AS n  -- Number of valid pairs excluding NULLs
FROM channel_sales GROUP BY channel;

-- To attach CORR at row level, OVER() is unavailable → use CTE GROUP BY + JOIN
-- ABS(CORR) allows positive and negative correlation to be compared on one strength scale

Watch the aggregation grain of GROUP BY: With GROUP BY channel, CORR is calculated from all rows within each channel. If you also need weekly correlations, adjust the grain to something like GROUP BY channel, week_group. If REGR_COUNT is smaller than expected, many NULLs may be reducing the reliability of the model.

Problem ▼

Using the channel_sales table, calculate, for each channel, the Pearson correlation coefficient (to 4 decimal places) between advertising cost (ad_cost) and revenue, the number of valid pairs (n), and a correlation-strength label (strength). Use ABS(CORR) >= 0.9 → 'Strong correlation', >= 0.7 → 'Moderate correlation', and 'Weak correlation' otherwise. Return the rows in descending corr_coef order.

Source table

► channel_sales (12 rows)

week	channel	ad_cost	revenue
1	Online	100	280
2	Online	200	650
3	Online	300	780
4	Online	400	1100
5	Online	500	1250
6	Online	600	1640
1	Offline	100	500
2	Offline	200	420
3	Offline	300	680
4	Offline	400	550
5	Offline	500	820
6	Offline	600	700

Expected Output ▼

channel	corr_coef	n	strength
Online	0.9914	6	Strong correlation
Offline	0.7498	6	Moderate correlation

Model Answer ▼

SELECT
  channel,
  ROUND(CORR(revenue, ad_cost)::numeric, 4)  AS corr_coef,  -- Pearson correlation coefficient
  REGR_COUNT(revenue, ad_cost)                 AS n,        -- Number of pairs where both columns are non-NULL
  CASE
    WHEN ABS(CORR(revenue, ad_cost)) >= 0.9 THEN 'Strong correlation'
    WHEN ABS(CORR(revenue, ad_cost)) >= 0.7 THEN 'Moderate correlation'
    ELSE                                           'Weak correlation'
  END AS strength
FROM   channel_sales
GROUP BY channel
ORDER BY corr_coef DESC;

/*
  Logical SQL evaluation order:
  1. FROM channel_sales       → read 12 rows
  2. GROUP BY channel         → split into 2 groups
  3. Aggregate CORR per group → calculate correlation coefficients
  4. CASE WHEN ABS(CORR)      → assign strength labels
  5. ROUND(::numeric, 4)      → round to 4 decimal places
  6. ORDER BY corr_coef DESC  → sort by correlation strength
  7. SELECT                   → output 2 rows
  */

Explanation (table transitions & key points) ▼

SELECT
  channel,
  ROUND(CORR(revenue, ad_cost)::numeric, 4)  AS corr_coef,
  REGR_COUNT(revenue, ad_cost) AS n,
  CASE
    WHEN ABS(CORR(revenue, ad_cost)) >= 0.9 THEN 'Strong correlation'
    WHEN ABS(CORR(revenue, ad_cost)) >= 0.7 THEN 'Moderate correlation'
    ELSE                                           'Weak correlation'
  END AS strength
FROM   channel_sales
GROUP BY channel
ORDER BY corr_coef DESC;

LEGEND ▼

Rows read / loaded

① FROM channel_sales — 6 Online rows and 6 Offline rows ▼

FROM channel_salesRead 12 rows of channel data. Online revenue rises almost proportionally as ad_cost increases, so a strong positive correlation is expected. Offline has more variation and therefore a moderate correlation.

1 / 5

week	► channel	► ad_cost	► revenue
1	Online	100	280
2	Online	200	650
3	Online	300	780
4	Online	400	1100
5	Online	500	1250
6	Online	600	1640
1	Offline	100	500
2	Offline	200	420
3	Offline	300	680
4	Offline	400	550
5	Offline	500	820
6	Offline	600	700

channel_sales: 12 rows (Online ×6 / Offline ×6)

LEARNING POINTS

Use CORR + GROUP BY for multi-axis screening: The analysis still fits in one query even when there are many groups. Simply extend the GROUP BY keys to include categories, regions, periods, and so on to survey which segments have strong linearity. CORR can be combined with GROUP BY, but it cannot be used as OVER() (a window function). To attach a correlation value at row level, use the two-step pattern CTE GROUP BY CORR → JOIN.

Check reliability with REGR_COUNT: REGR_COUNT(Y, X) returns the number of valid pairs after excluding pairs where either value is NULL. A large gap between COUNT(*) and REGR_COUNT signals frequent missing data. In general, when n < 10, the confidence interval for CORR is wide, so conclusions should be held back.

Use ABS(CORR) to compare mixed-sign segments on one scale: A strong negative correlation such as inventory and stockout rate (r = −0.92) can also be classified as 'Strong correlation' by ABS(CORR) >= 0.9. Interpret the sign for business meaning and use ABS for the strength category.

ANTI-PATTERNS

Overtrust CORR from a small group: With a small sample such as n=4, chance agreement can produce an extreme CORR value (for example, |CORR|=1 is guaranteed when n=2). For groups with n < 10, mark the value as a reference only and prioritize collecting more data; this is the responsible practical response.

Conclude causation from a correlation coefficient alone: Even if Offline has CORR=0.75, you cannot assert that increasing ad spend will increase sales. Correlation measures the strength of co-movement but does not prove causation. Causal inference requires a randomized experiment (A/B test), instrumental variables, or another appropriate design.

Practical Column: Applying this to channel-effect screening

GROUP BY CORR can screen the linearity of advertising effects across channels, regions, and product categories at once. Channels with high correlation can move to the next step, quantifying ROI with REGR_SLOPE; channels with low correlation can be investigated individually for a nonlinear relationship or a data-quality problem. It serves as an entry point to an analysis workflow. Adding HAVING REGR_COUNT(Y,X) >= 20 filters out groups with too few samples and automatically excludes unreliable CORR values from the result.

QUESTION 7

Residual analysis — Attach predictions and residuals to every row with REGR_SLOPE + CROSS JOIN to identify outliers

REGR_SLOPECROSS JOINResidual AnalysisOutlier DetectionMulti-stage CTE

Background ▼

Attaching the regression residual (residual = observed value − predicted value) to each row helps identify rows that deviate substantially from the model, which are candidates for outliers or anomalies. A standard SQL pattern is aggregate in a CTE (one row) → expand to every row with CROSS JOIN → calculate predictions and residuals.

-- ① Aggregate the model parameters into one row in a CTE
WITH model AS (
  SELECT REGR_SLOPE(Y, X) AS slope, REGR_INTERCEPT(Y, X) AS intercept
  FROM data_table
),
-- ② Expand the parameters to every row with CROSS JOIN → calculate predictions and residuals
with_pred AS (
  SELECT d.*, ROUND((m.slope * d.X + m.intercept)::numeric, 0) AS predicted,
         ROUND((d.Y - (m.slope * d.X + m.intercept))::numeric, 0) AS residual
  FROM data_table d CROSS JOIN model m  -- 1 row × N rows = N rows
)
SELECT *, ABS(residual) AS abs_residual FROM with_pred ORDER BY abs_residual DESC;

The sign and meaning of a residual: A positive residual (observed > predicted) means the model underestimates; a negative residual (observed < predicted) means the model overestimates. Rows with a large ABS(residual) are outlier candidates. Also check REGR_R2 to see whether outliers are lowering the coefficient of determination.

Problem ▼

Using the store_data table, build a linear regression model from floor area (floor_sqm) to daily sales (daily_sales) in a CTE. Calculate each store's predicted sales (predicted), residual (residual), and absolute residual (abs_residual). Return the rows in descending abs_residual order and identify the store that is an outlier candidate.

Source table

► store_data (8 rows)

store_id	floor_sqm	daily_sales
S1	100	500
S2	200	950
S3	300	1400
S4	400	1850
S5	500	2300
S6	600	2200
S7	700	3200
S8	800	3700

Expected Output ▼

store_id	floor_sqm	daily_sales	predicted	residual	abs_residual
S6	600	2200	2664	-464	464
S8	800	3700	3533	167	167
S7	700	3200	3099	101	101
S5	500	2300	2230	70	70
S4	400	1850	1795	55	55
S3	300	1400	1361	39	39
S2	200	950	926	24	24
S1	100	500	492	8	8

Model Answer ▼

WITH model AS (
  -- Calculate regression parameters from all store data at once
  SELECT
    REGR_SLOPE(daily_sales, floor_sqm)     AS slope,
    REGR_INTERCEPT(daily_sales, floor_sqm) AS intercept,
    REGR_R2(daily_sales, floor_sqm)        AS r2
  FROM store_data
),
with_pred AS (
  -- Expand the parameters to every row with CROSS JOIN → calculate each store's prediction and residual
  SELECT
    s.store_id,
    s.floor_sqm,
    s.daily_sales,
    ROUND((m.slope * s.floor_sqm + m.intercept)::numeric, 0) AS predicted,
    ROUND((s.daily_sales - (m.slope * s.floor_sqm + m.intercept))::numeric, 0) AS residual
  FROM   store_data s
  CROSS JOIN model m               -- 1 row × 8 rows = 8 rows
)
SELECT
  store_id, floor_sqm, daily_sales,
  predicted,
  residual,
  ABS(residual) AS abs_residual    -- outlier score (larger means a stronger candidate)
FROM   with_pred
ORDER BY abs_residual DESC;

/*
  Logical SQL evaluation order:
  1. CTE model — FROM store_data  → read 8 rows
  2. REGR_SLOPE(...)              → calculate the slope
  3. REGR_INTERCEPT               → calculate the intercept
  4. REGR_R2                      → calculate the coefficient of determination
  5. CTE with_pred — CROSS JOIN   → join the model to every row
  6. predicted                    → calculate predicted values
  7. residual                     → calculate residuals
  8. ABS(residual)                → calculate the outlier score
  9. ORDER BY abs_residual DESC   → sort by largest deviation
  10. SELECT                      → output 8 rows
  */

Explanation (table transitions & key points) ▼

WITH model AS (
  SELECT
    REGR_SLOPE(daily_sales, floor_sqm)     AS slope,
    REGR_INTERCEPT(daily_sales, floor_sqm) AS intercept,
    REGR_R2(daily_sales, floor_sqm)        AS r2
  FROM   store_data
),
with_pred AS (
  SELECT
    s.store_id, s.floor_sqm, s.daily_sales,
    ROUND((m.slope * s.floor_sqm + m.intercept)::numeric, 0) AS predicted,
    ROUND((s.daily_sales - (m.slope * s.floor_sqm + m.intercept))::numeric, 0) AS residual
  FROM   store_data s
  CROSS JOIN model m
)
SELECT
  store_id, floor_sqm, daily_sales,
  predicted, residual,
  ABS(residual) AS abs_residual
FROM   with_pred
ORDER BY abs_residual DESC;

LEGEND ▼

Rows read / loaded

① FROM store_data — floor area and daily sales for 8 stores (S6 is a candidate) ▼

FROM store_dataRead the floor area (floor_sqm) and daily sales (daily_sales) for 8 stores. S1–S5, S7, and S8 follow an almost linear trend, while S6 alone has only 2,200 in sales despite its 600m² area.

1 / 5

store_id	► floor_sqm	► daily_sales
S1	100	500
S2	200	950
S3	300	1400
S4	400	1850
S5	500	2300
S6	600	2,200 ⚠
S7	700	3200
S8	800	3700

store_data: 8 rows (S6 is an outlier candidate — sales are low for its area)

LEARNING POINTS

When CROSS JOIN is safe: CROSS JOIN generates a Cartesian product (M×N rows). It is safe only when the CTE is guaranteed to contain one row, as it does here with an aggregate result. If the CTE might contain multiple rows, avoid CROSS JOIN and use JOIN ... ON 1=1 or LATERAL to make the intent explicit.

Read business insight from the sign of a residual: Negative residual (observed < predicted) means the model overestimates and the store is below the average for its size. S6's −464 means that a 600m² store earns 464 less than the expected 2,664 in sales, making it a trigger for further investigation into competitors, location, product strength, and so on.

Recalculate and compare R² after removing an outlier: Recalculating REGR_R2 for the remaining seven stores after excluding S6 produces a substantial improvement. Create a parallel CTE with WHERE store_id != 'S6' and quantify the outlier's influence through the change in R²; this is a practical finishing step for residual analysis.

ANTI-PATTERNS

Allow the CTE on the right side of CROSS JOIN to return multiple rows: If a CTE without an appropriate WHERE or GROUP BY can return multiple rows depending on the data, CROSS JOIN generates a Cartesian product and multiplies the result. Guarantee at design time that the other side has exactly one row, and make a habit of commenting “one row only.”

Immediately delete an outlier as “bad data”: S6's low sales may be an input error, but it may also have a valid explanation such as renovation work or a nearby competitor. Treat an outlier as the starting point for investigation, not an automatic deletion; confirm it with domain knowledge before deciding whether to remove it.

Practical Column: Applying this to quality management and KPI anomaly detection

Residual analysis is a general pattern for detecting deviations from a benchmark: monitoring defects on a production line, detecting departures from sales targets, and flagging anomalies in advertising effectiveness are all examples. You can flag ABS(residual) > 2 * STDDEV(residual) (a deviation of at least 2σ), or automatically alert on the top 10 absolute residuals in a daily batch, using the same SQL structure. It is also common in practice to monitor REGR_R2 weekly and use a sudden drop in R² as a trigger to update the model.

QUESTION 8

Category-specific regression models + batch prediction — Build models with GROUP BY REGR and apply them to new data at once

REGR_SLOPEREGR_R2Category RegressionBatch PredictionGROUP BY + JOIN

Background ▼

Combining GROUP BY with REGR_SLOPE and REGR_INTERCEPT builds an independent regression model for every category in one query. You can then join a new-data table with JOIN to batch-process predictions for multiple scenarios.

-- Build category-specific models in parallel with GROUP BY
WITH models AS (
  SELECT category,
    REGR_SLOPE(sales, ad_cost)     AS slope,
    REGR_INTERCEPT(sales, ad_cost) AS intercept,
    REGR_R2(sales, ad_cost)        AS r2
  FROM product_data GROUP BY category
)
-- Apply each category model to new data with JOIN for batch prediction
SELECT f.category, f.future_ad,
  ROUND((m.slope * f.future_ad + m.intercept)::numeric, 0) AS predicted_sales
FROM forecast_input f JOIN models m USING (category);

A simple-regression slope can be interpreted as a category-specific ROI proxy: slope = Δsales / Δad spend is the “incremental sales per unit of ad spend” and a proxy for advertising ROI. A category with a higher slope has a higher advertising return, so slope can support budget allocation. Make a habit of checking R² and making decisions only for categories whose models are reliable.

Problem ▼

Using the product_data table, build a regression model from advertising cost (ad_cost) to sales, for each category. Then apply each category's model to the forecast advertising costs (future_ad) in forecast_input, and output predicted sales (predicted_sales), slope, intercept, and coefficient of determination (r2). Return rows in ascending category and future_ad order.

Source tables

► product_data (10 rows)

category	ad_cost	sales
Electronics	100	300
Electronics	200	720
Electronics	300	1000
Electronics	400	1420
Electronics	500	1660
Fashion	100	220
Fashion	200	300
Fashion	300	480
Fashion	400	490
Fashion	500	680

► forecast_input (4 rows)

category	future_ad
Electronics	400
Electronics	600
Fashion	400
Fashion	600

Expected Output ▼

category	future_ad	predicted_sales	slope	intercept	r2
Electronics	400	1362	3.4200	-6.00	0.9926
Electronics	600	2046	3.4200	-6.00	0.9926
Fashion	400	545	1.1100	101.00	0.9513
Fashion	600	767	1.1100	101.00	0.9513

Model Answer ▼

WITH model_by_cat AS (
  -- Build independent regression models in parallel with GROUP BY category
  SELECT
    category,
    REGR_SLOPE(sales, ad_cost)     AS slope,
    REGR_INTERCEPT(sales, ad_cost) AS intercept,
    REGR_R2(sales, ad_cost)        AS r2
  FROM   product_data
  GROUP BY category
)
SELECT
  f.category,
  f.future_ad,
  ROUND((m.slope * f.future_ad + m.intercept)::numeric, 0) AS predicted_sales,
  ROUND(m.slope::numeric,     4) AS slope,      -- Incremental sales per unit of ad spend (ROI proxy)
  ROUND(m.intercept::numeric, 2) AS intercept,
  ROUND(m.r2::numeric,         4) AS r2         -- Check model reliability
FROM   forecast_input f
JOIN   model_by_cat m USING (category)            -- Join the matching model to each row by category
ORDER BY f.category, f.future_ad;

/*
  Logical SQL evaluation order:
  1. CTE model_by_cat — FROM product_data  → read 10 rows
  2. GROUP BY category                     → split into 2 groups
  3. Aggregate REGR per group              → calculate slope/intercept/R2
  4. FROM forecast_input                   → read 4 rows
  5. JOIN model_by_cat USING (category)    → join the models
  6. predicted_sales                       → calculate predictions
  7. ORDER BY category, future_ad          → final sort
  8. SELECT                                → output 4 rows
  */

Explanation (table transitions & key points) ▼

WITH model_by_cat AS (
  SELECT
    category,
    REGR_SLOPE(sales, ad_cost)     AS slope,
    REGR_INTERCEPT(sales, ad_cost) AS intercept,
    REGR_R2(sales, ad_cost)        AS r2
  FROM   product_data
  GROUP BY category
)
SELECT
  f.category,
  f.future_ad,
  ROUND((m.slope * f.future_ad + m.intercept)::numeric, 0) AS predicted_sales,
  ROUND(m.slope::numeric, 4) AS slope,
  ROUND(m.intercept::numeric, 2) AS intercept,
  ROUND(m.r2::numeric, 4) AS r2
FROM   forecast_input f
JOIN   model_by_cat m USING (category)
ORDER BY f.category, f.future_ad;

LEGEND ▼

Rows read / loaded

① FROM product_data — training data for all categories ▼

FROM product_dataGroup the 10 rows by category. Electronics (steep slope) and Fashion (gentle slope) have very different linear relationships, so category-specific models are more accurate and interpretable than one model for all rows.

1 / 5

category	► ad_cost	► sales
Electronics	100	300
Electronics	200	720
Electronics	300	1000
Electronics	400	1420
Electronics	500	1660
Fashion	100	220
Fashion	200	300
Fashion	300	480
Fashion	400	490
Fashion	500	680

product_data: 10 rows (Electronics ×5 / Fashion ×5)

LEARNING POINTS

Slope as an advertising ROI proxy: The slope of a simple regression is the change in the dependent variable Y when explanatory variable X increases by one unit. Here, slope = incremental sales per unit of ad spend, so it functions as a proxy for advertising return on investment. Electronics (3.42) has a slope about three times Fashion (1.11), providing quantitative support for concentrating the same advertising budget on Electronics.

JOIN with USING vs ON: USING (category) is shorthand when the join key has the same name and type in both tables. The result contains only one category column, unlike ON f.category = m.category. Use ON when you need to assign distinct aliases explicitly in SELECT.

Check R² before using model predictions: Predictions from a category with low R² are not reliable enough for decision-making. Treat R² < 0.7 as a reference value and prioritize gathering more data or considering a nonlinear model. Both categories here have R² > 0.95, so their predictions are sufficiently reliable.

ANTI-PATTERNS

Mix every category into one regression: If Electronics and Fashion are combined into one REGR_SLOPE without GROUP BY, two different linear relationships are averaged into a model that represents neither reality. Regression across mixed groups can cause Simpson's paradox, so always separate segments with GROUP BY.

Extrapolate far outside the training-data range: Electronics is trained on ad_cost=100–500. future_ad=600 is a small extrapolation, but a prediction at a value such as 1,000 is not reliable. Always annotate the extrapolated range so users understand the risk.

Practical Column: Automating budget-scenario planning

Extending this structure can generate all 30 combinations of “next period advertising budget scenarios (5 million, 7 million, and 10 million) × product categories (10 types)” in one query. Insert every scenario row into forecast_input, and the JOIN after the CTE builds predictions for all combinations automatically. You can also calculate predicted profit with predicted_sales × margin_rate, making this directly useful as a query that generates a budget-allocation simulation report for management meetings.

QUESTION 9

Order statistics and outlier detection — Identify IQR-based outliers with PERCENTILE_CONT and Tukey fences

PERCENTILE_CONTWITHIN GROUPQuartilesOutlier DetectionTukey Fences

Background ▼

PERCENTILE_CONT(p) WITHIN GROUP (ORDER BY col) is an ordered-set aggregate function that sorts the data in ascending order and returns the value at position p (0–1) by linear interpolation. Q1, the median, and Q3 are obtained with p=0.25, 0.5, and 0.75.

-- Syntax: PERCENTILE_CONT(percentile) WITHIN GROUP (ORDER BY column)
-- Linear interpolation: row position = 1 + p×(N−1), e.g. N=12, p=0.25 → position=3.75
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY score) AS q1
PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY score) AS median
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY score) AS q3

-- PERCENTILE_DISC: return the nearest actual value without interpolation (discrete version)
-- Tukey fences (box-plot outlier rule):
--   lower_fence = Q1 − 1.5×IQR / upper_fence = Q3 + 1.5×IQR
--   Values outside the fences are treated as outliers

It can be grouped as an aggregate: PERCENTILE_CONT can be combined with GROUP BY category to calculate medians by category at once, but it cannot be used as an OVER() window function. NTILE is an alternative when you need to attach a percentile band to every row, but remember the difference: NTILE divides by row count, whereas PERCENTILE_CONT interpolates by value.

Problem ▼

Using the exam_results table, calculate IQR, the lower fence, and the upper fence from the first quartile, median, and third quartile in a CTE. Label whether each student's score is within the Tukey fences (lower=Q1−1.5×IQR, upper=Q3+1.5×IQR) with outlier_flag ('Outlier' / 'Normal'). Return the rows in ascending score order.

Source table

► exam_results (12 rows)

student_id	score
S01	67
S02	82
S03	55
S04	138
S05	63
S06	80
S07	42
S08	91
S09	71
S10	75
S11	85
S12	58

Expected Output ▼

student_id	score	lower_fence	upper_fence	outlier_flag
S07	42	30.25	114.25	Normal
S03	55	30.25	114.25	Normal
S12	58	30.25	114.25	Normal
S05	63	30.25	114.25	Normal
S01	67	30.25	114.25	Normal
S09	71	30.25	114.25	Normal
S10	75	30.25	114.25	Normal
S06	80	30.25	114.25	Normal
S02	82	30.25	114.25	Normal
S11	85	30.25	114.25	Normal
S08	91	30.25	114.25	Normal
S04	138	30.25	114.25	Outlier

Model Answer ▼

WITH stats AS (
  -- Calculate Q1, median, and Q3 by linear interpolation with PERCENTILE_CONT
  SELECT
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY score) AS q1,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY score) AS median,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY score) AS q3
  FROM exam_results
),
fences AS (
  -- Calculate Tukey fences (the outlier thresholds)
  SELECT
    q1, median, q3,
    q3 - q1                  AS iqr,
    q1 - 1.5 * (q3 - q1)    AS lower_fence,
    q3 + 1.5 * (q3 - q1)    AS upper_fence
  FROM stats
)
SELECT
  e.student_id,
  e.score,
  ROUND(f.lower_fence::numeric, 2) AS lower_fence,
  ROUND(f.upper_fence::numeric, 2) AS upper_fence,
  CASE
    WHEN e.score < f.lower_fence OR e.score > f.upper_fence
    THEN 'Outlier'
    ELSE 'Normal'
  END AS outlier_flag
FROM   exam_results e
CROSS JOIN fences f
ORDER BY e.score;

/*
  Logical SQL evaluation order:
  1. CTE stats — FROM exam_results  → read 12 rows
  2. PERCENTILE_CONT(0.25)          → interpolate Q1
  3. PERCENTILE_CONT(0.50)          → interpolate the median
  4. PERCENTILE_CONT(0.75)          → interpolate Q3
  5. CTE fences                     → calculate outlier boundaries from IQR
  6. CROSS JOIN fences              → attach boundaries to every row
  7. CASE                           → classify outliers
  8. ORDER BY score                 → sort ascending
  9. SELECT                         → output 12 rows
  */

Explanation (table transitions & key points) ▼

WITH stats AS (
  SELECT
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY score) AS q1,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY score) AS median,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY score) AS q3
  FROM exam_results
),
fences AS (
  SELECT q1, median, q3,
    q3 - q1                  AS iqr,
    q1 - 1.5 * (q3 - q1)    AS lower_fence,
    q3 + 1.5 * (q3 - q1)    AS upper_fence
  FROM stats
)
SELECT
  e.student_id, e.score,
  ROUND(f.lower_fence::numeric, 2) AS lower_fence,
  ROUND(f.upper_fence::numeric, 2) AS upper_fence,
  CASE
    WHEN e.score < f.lower_fence OR e.score > f.upper_fence
    THEN 'Outlier'
    ELSE 'Normal'
  END AS outlier_flag
FROM   exam_results e
CROSS JOIN fences f
ORDER BY e.score;

LEGEND ▼

Rows read / loaded

① FROM exam_results — sort 12 scores ascending ▼

FROM exam_results ORDER BY scoreSort the 12 exam scores in ascending order. S04 (138 points) is isolated above the others and is an outlier candidate. PERCENTILE_CONT calculates percentiles by linearly interpolating from positions in this sorted order.

1 / 6

Row number	student_id	► score (ascending)
1	S07	42
2	S03	55
3	S12	58
4	S05	63
5	S01	67
6	S09	71
7	S10	75
8	S06	80
9	S02	82
10	S11	85
11	S08	91
12	S04	138 ⚠

exam_results: 12 rows (sorted ascending / S04 is the largest isolated value)

LEARNING POINTS

PERCENTILE_CONT vs PERCENTILE_DISC: PERCENTILE_CONT returns a value between rows through linear interpolation (Q1=61.75 here does not appear in the actual data). PERCENTILE_DISC returns the smallest actual data value at or above the percentile without interpolation (so integer scores always produce an integer). CONT is intuitive for continuous data and DISC for discrete data. Both use the same WITHIN GROUP (ORDER BY col) syntax.

Meaning of Tukey's 1.5×IQR rule: IQR is the width of the central 50% of the data. Extending the normal range 1.5 times the IQR beyond that center contains approximately 99.3% of data close to a normal distribution. A 3×IQR fence is also used for “extreme outliers”; adjust the coefficient for the business requirement. The IQR-based approach is robust (less affected by outliers) compared with the 2σ or 3σ rules using mean and standard deviation.

Category-specific outlier detection with GROUP BY: Add GROUP BY as in PERCENTILE_CONT ... WITHIN GROUP ... FROM exam_results GROUP BY exam_type to set an independent fence for every exam type. This pattern is essential when outlier thresholds differ by subject, department, or period.

ANTI-PATTERNS

Detect outliers with mean ±2σ (standard deviation): A ±2σ rule using AVG and STDDEV_SAMP is distorted by the outlier itself because it shifts both the mean and standard deviation; the more outliers there are, the lower the detection sensitivity becomes. Use IQR-based Tukey fences for robust detection. S04 (138) affects the mean (75.6) and sample standard deviation (24.1), making the standard-deviation rule too permissive.

Delete a row without investigating after adding an outlier flag: The data alone cannot tell whether S04's score of 138 is an input error (it should be 38) or a genuinely exceptional score. Use the outlier flag as a trigger for investigation rather than an immediate deletion, and decide what to do after domain-expert review.

Practical Column: Applying this to data-quality monitoring

PERCENTILE_CONT + Tukey fences can be applied to ETL-pipeline data-quality checks, KPI anomaly alerts, sensor-data outlier detection, and many other use cases. A standard automated quality-monitoring pattern is to save the previous month's IQR fences as a baseline in a monthly batch, compare them with the current month's data, and count the outliers. You can also output post-outlier-removal statistics such as average and total in separate columns with FILTER (WHERE outlier_flag = 'Normal'), completing a before-and-after KPI comparison in one query.

QUESTION 10

Moving average — Calculate a 3-month moving average and month-over-month change with a ROWS BETWEEN window frame

ROWS BETWEENAVG OVERMoving AverageTime-series AnalysisLAG + NULLIF

Background ▼

Explicitly specifying a window frame with ROWS BETWEEN n PRECEDING AND CURRENT ROW calculates a moving average over the most recent n+1 rows, including the current row.

-- Explicitly specify the moving-average window frame with ROWS BETWEEN
AVG(revenue) OVER (
  ORDER BY month
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS ma3                -- 3-month moving average (latest 3 rows including current month)

-- Difference from the default frame:
-- ORDER BY alone: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
--   → cumulative average from the first row through the current month
-- ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
--   → only the latest 3 physical rows (moving average) ← this is the correct frame

-- Month-over-month pattern using NULLIF to prevent division by zero (LAG extension)
-- (revenue - prev_revenue)::numeric / NULLIF(prev_revenue, 0) * 100

Difference between ROWS and RANGE: The default frame for a window function with ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (a cumulative frame that also includes rows with the same value). To specify “the most recent N physical rows” for a moving average, always write ROWS BETWEEN explicitly. The results differ when the ORDER BY column contains duplicate values.

Problem ▼

Using the sales_monthly table, attach the previous month's sales (prev_revenue) with LAG in a CTE. In the outer query, calculate both a 3-month moving average (ma3: the latest 3 rows, rounded to an integer) and the month-over-month growth rate (mom_pct: 2 decimal places, with NULLIF to prevent division by zero). Return rows in ascending month order.

Source table

► sales_monthly (8 rows)

month	revenue
2024-01	1200000
2024-02	1350000
2024-03	1180000
2024-04	1520000
2024-05	1680000
2024-06	1450000
2024-07	1820000
2024-08	1960000

Expected Output ▼

month	revenue	ma3	mom_pct
2024-01	1200000	1200000	NULL
2024-02	1350000	1275000	12.50
2024-03	1180000	1243333	-12.59
2024-04	1520000	1350000	28.81
2024-05	1680000	1460000	10.53
2024-06	1450000	1550000	-13.69
2024-07	1820000	1650000	25.52
2024-08	1960000	1743333	7.69

Model Answer ▼

WITH lagged AS (
  -- Attach previous-month sales with LAG (fix it in the CTE for reuse in the outer query)
  SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_revenue
  FROM sales_monthly
)
SELECT
  month,
  revenue,
  ROUND(
    AVG(revenue) OVER (
      ORDER BY month
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- moving average of the latest 3 rows
    )::numeric
  , 0) AS ma3,
  ROUND(
    (revenue - prev_revenue)::numeric
    / NULLIF(prev_revenue, 0) * 100             -- prevent division by zero with NULLIF
  , 2) AS mom_pct
FROM   lagged
ORDER BY month;

/*
  Logical SQL evaluation order:
  1. CTE lagged — FROM sales_monthly   → read 8 rows
  2. LAG(revenue, 1) OVER (...)        → attach the previous month's revenue
  3. FROM lagged                       → reference the CTE
  4. AVG(revenue) OVER (... ROWS ...)  → calculate the 3-row moving average
  5. NULLIF(prev_revenue, 0)           → prevent division by zero
  6. (revenue - prev) / prev * 100     → calculate month-over-month growth
  7. ROUND(...)                        → round the results
  8. ORDER BY month                    → sort in chronological order
  9. SELECT                            → output 8 rows
  */

Explanation (table transitions & key points) ▼

WITH lagged AS (
  SELECT
    month, revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_revenue
  FROM   sales_monthly
)
SELECT
  month, revenue,
  ROUND(
    AVG(revenue) OVER (
      ORDER BY month
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    )::numeric
  , 0) AS ma3,
  ROUND(
    (revenue - prev_revenue)::numeric
    / NULLIF(prev_revenue, 0) * 100
  , 2) AS mom_pct
FROM   lagged
ORDER BY month;

LEGEND ▼

Rows read / loaded

① FROM sales_monthly — monthly revenue data ▼

FROM sales_monthlyRead 8 monthly revenue rows from January through August 2024.

1 / 5

month	► revenue
2024-01	1,200,000
2024-02	1,350,000
2024-03	1,180,000
2024-04	1,520,000
2024-05	1,680,000
2024-06	1,450,000
2024-07	1,820,000
2024-08	1,960,000

sales_monthly: 8 rows

LEARNING POINTS

The decisive difference between ROWS and RANGE: When only ORDER BY is specified, PostgreSQL uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (a cumulative frame) by default. AVG then means “the average of every row from the first through the current month,” not a moving average. Always specify ROWS BETWEEN n PRECEDING AND CURRENT ROW for a moving average.

Prevent division by zero with NULLIF: NULLIF(expr, 0) returns NULL when expr is 0 and prevents an error from dividing by zero. Because mom_pct would fail when the previous month's sales are 0, NULLIF should always be applied to the result of LAG. Intentionally using NULL propagation lets the calculation skip such cases without an error.

Choose the width of the moving-average window: A 3-month MA (ma3) captures a short trend when data changes quickly, while 6- or 12-month MAs smooth seasonality. Change to ROWS BETWEEN 5 PRECEDING AND CURRENT ROW for a 6-month MA or ROWS BETWEEN 11 PRECEDING AND CURRENT ROW for a 12-month MA. You can place multiple moving averages in one query to detect a short-term/long-term golden cross.

ANTI-PATTERNS

Omit ROWS BETWEEN and try to calculate a moving average with ORDER BY alone: AVG(revenue) OVER (ORDER BY month) uses RANGE (a cumulative average) by default. If the average keeps increasing every month for no apparent reason, suspect that ROWS BETWEEN is missing. Explicitly specifying ROWS BETWEEN is mandatory for a moving average.

Assume ROWS BETWEEN represents calendar months when monthly data has gaps: If 2024-03 is missing, ROWS BETWEEN 2 PRECEDING for 2024-04 uses 2024-02 and 2024-01 as the two preceding rows. It counts physical rows, not calendar months. If months are missing, fill a calendar with GENERATE_SERIES or use an approach based on date differences instead of relying on the row count.

Practical Column: Standard design for a time-series dashboard

Combining ROWS BETWEEN and LAG lets you put MA3, MA6, month-over-month change, and year-over-year change (LAG(revenue,12)) into four columns of one time-series KPI dashboard query. Adding PARTITION BY product_category to the ROWS BETWEEN OVER clause also calculates category-specific moving averages in the same query, making the result directly useful as a BI-tool data-supply query. You can even detect the month when MA begins to exceed revenue as a trend-reversal point with a single query such as CASE WHEN revenue > ma3 AND LAG(revenue) <= LAG(ma3).

student_id	score
S01	67
S02	82
S03	55
S04	138
S05	63
S06	80
S07	42
S08	91
S09	71
S10	75
S11	85
S12	58

student_id	score
S01	67
S02	82
S03	55
S04	138
S05	63
S06	80
S07	42
S08	91
S09	71
S10	75
S11	85
S12	58

student_id	score
S01	67
S02	82
S03	55
S04	138
S05	63
S06	80
S07	42
S08	91
S09	71
S10	75
S11	85
S12	58