Wednesday, September 23, 2026

Morphological Operations — The Shape Mechanics of Computer Vision
Computer Vision · 1960s idea · Still everywhere

Morphological
Operations

The little shape-machines that teach pixels to clean up, connect, separate and reveal objects.
FROM SET THEORY → TO IMAGE PROCESSING

1 Why were these invented?

Normal image processing often asks: “What is the value of this pixel?”

Morphology asks a different question:

“What shape is living around this pixel, and how should that shape change?”

That shift is the whole trick. A tiny structuring element acts like a geometric probe. Move it across the image and use a rule to grow, shrink, connect, disconnect, fill or extract shapes.

2 How did the idea happen?

Before CV

Geometry

Mathematics already knew how to reason about sets, shapes, boundaries and neighborhoods.

1960s

Matheron

Georges Matheron developed mathematical morphology around lattice/set ideas for analyzing shapes.

1960s–70s

Serra

Jean Serra helped build the theory and connect it strongly to practical image analysis.

Computer era

Binary images

The rules became computationally useful: probe an object with a small structuring element.

Today

CV pipelines

OCR, document cleanup, segmentation, metrology, microscopy, inspection and more.

Historical note: mathematical morphology grew from set theory and lattice-based thinking; its modern mathematical form is strongly associated with Matheron and Serra.

3 The one object you must understand

Structuring Element (SE)

It is a small shape used to probe the neighborhood of each pixel.

SE = a tiny geometric question ↓ “Does the image fit this shape here?”

Common SEs:

■ 3×3 square · · · · ● · 3×3 cross · · · ○ disk / ellipse

4 The binary-image intuition

For binary morphology, think:

  • Foreground = object / 1 / white
  • Background = 0 / black
  • The SE slides over the image.
  • The operation checks how the SE and foreground overlap.
Dilation asks “Can I place the SE here and touch the object?”

Erosion asks “Can the SE fit completely inside the object here?”

5 The morphology toolbox

Click an operation. The panel below changes. Learn the job, not just the name.

6 The tiny morphology laboratory

Watch a synthetic binary image change. This is deliberately simple: the goal is to see the geometry.

BEFORE
AFTER

The demo uses a square structuring element and binary rules. Real libraries such as OpenCV also support different kernels/shapes, anchors and grayscale morphology.

7 Why each operation exists

OperationRecipeShape effectTypical job
DilationGrow foregroundThicker, larger, gaps may connectBridge broken parts, expand masks
ErosionShrink foregroundThinner, smaller, narrow links may disappearRemove small objects, separate touching regions
OpeningErosion → DilationRemoves small foreground objects while restoring broad shapeNoise cleanup
ClosingDilation → ErosionFills small holes/gaps while restoring broad shapeSeal broken contours
GradientDilation − ErosionProduces a boundary bandEdge/boundary extraction
Top-HatOriginal − OpeningExtracts small bright detailsUneven background / small bright feature detection
Black-HatClosing − OriginalExtracts small dark detailsDark gaps/text on bright backgrounds
Hit-or-MissForeground + background patternDetects a precise binary configurationPattern, endpoint/corner-style shape detection

8 The “don't memorize blindly” map

DILATION → grow EROSION → shrink OPENING → remove small bright/foreground junk erosion → dilation CLOSING → close small dark gaps/holes dilation → erosion GRADIENT → boundary dilation − erosion TOP-HAT → small bright detail original − opening BLACK-HAT → small dark detail closing − original HIT-OR-MISS → exact local pattern

Shape before pixels

If you remember only one idea:

“Morphology is not primarily about changing pixel values. It is about manipulating the geometry of shapes.”

Then ask three questions:

  1. What is my foreground?
  2. What shape is my structuring element?
  3. Do I want to grow, shrink, clean, close, or extract?

9 Quick challenge — can you predict the tool?

1. Tiny white specks are polluting a binary mask. → Opening
2. A character stroke has tiny black gaps. → Closing
3. You need the outer boundary of a mask. → Morphological gradient
4. You want to detect a very specific local binary pattern. → Hit-or-Miss
Morphology in one sentence: “Use a small geometric probe to make large, useful decisions about image shape.”

Vintage computer science. Surprisingly modern. ✦

Monday, September 21, 2026

SVM → One-Class SVM — Vintage ML Field Notebook
Machine Learning Field Notebook · No. 07

SVM → One-Class SVM

How a margin classifier turns into a machine that learns “normal.”
MATH • INTUITION • HAND CALCULATION

The story: ordinary SVM asks, “Which side of the boundary is this object on?” One-Class SVM asks, “Does this object look like the population I was told is normal?”

Ordinary SVM

You have labelled examples: +1 and −1. Find a boundary with the largest useful margin.

One-Class SVM

You mainly have normal examples. Learn a region around them. New points outside that learned region are candidates for novelty.

Realistic ASM example: imagine historical internet-facing assets described by exposed ports, service count, request rate, certificate age and vulnerability count. One-Class SVM can learn the shape of the normal asset population and flag unusual assets for investigation.

1. Start with ordinary SVM

For labelled data, the linear decision boundary is:

wᵀx + b = 0

For a binary label y ∈ {−1,+1}, SVM requires:

yᵢ(wᵀxᵢ + b) ≥ 1

The canonical margin planes are:

wᵀx + b = +1 wᵀx + b = −1

The distance between them is 2 / ||w||. Therefore:

min ½||w||² subject to yᵢ(wᵀxᵢ+b) ≥ 1
Why square the norm? The margin is 2/||w||. Minimizing ½||w||² gives the same optimum while producing a convenient differentiable objective.

2. The geometry behind the formula

decision boundary+1 margin−1 marginmargin = 2 / ||w||

Think of w as the arrow perpendicular to the boundary. Make ||w|| smaller → the margin gets wider.

Dual form: the bridge to kernels

maximize Σᵢ αᵢ − ½ ΣᵢΣⱼ αᵢαⱼ yᵢyⱼ K(xᵢ,xⱼ) subject to αᵢ ≥ 0, Σᵢ αᵢyᵢ = 0
f(x) = Σᵢ αᵢ yᵢ K(xᵢ,x) + b

The kernel trick replaces the dot product with a similarity function K without explicitly constructing φ(x).

3. Reality arrives: Soft Margin

yᵢ(wᵀxᵢ+b) ≥ 1 − ξᵢ ξᵢ ≥ 0 min ½||w||² + C Σᵢ ξᵢ
SituationξInterpretation
Correct, outside margin0No violation
Correct, inside margin0 < ξ < 1Margin violation
On boundary1Boundary point
Wrong sideξ > 1Misclassification

C controls the cost assigned to violations. Large C penalizes violations strongly; smaller C allows more violations in exchange for regularization.

4. The conceptual jump: One-Class SVM

Now suppose you have 1,000 known-normal assets but almost no confirmed attack examples.

“Find a boundary that contains the normal population, while allowing a controlled fraction of observations outside.”

A standard ν-One-Class SVM formulation is:

min ½||w||² + (1/(νn)) Σᵢ ξᵢ − ρ subject to wᵀφ(xᵢ) ≥ ρ − ξᵢ ξᵢ ≥ 0

The decision score is:

f(x) = wᵀφ(x) − ρ f(x) ≥ 0 → inlier / normal-like f(x) < 0 → outlier / novel
Important: One-Class SVM does not learn “malicious” automatically. It learns the training population you define as normal. Contaminated training data can therefore teach the wrong notion of normality.

Why ν matters

In the standard formulation, ν is an upper bound on the fraction of training errors and a lower bound on the fraction of support vectors.

5. Tiny hand calculation: RBF similarity

Before solving the full optimization problem, calculate the kernel quantity it repeatedly uses.

x₁ = (1, 2) x₂ = (2, 3) γ = 0.5 K(x,z) = exp(−γ ||x−z||²)
x₁ − x₂ = (−1,−1) ||x₁−x₂||² = 1 + 1 = 2 K(x₁,x₂) = exp(−0.5 × 2) = exp(−1) ≈ 0.368

So their RBF similarity is about 0.368. Identical points have K=1; increasingly distant points approach 0.

Interactive: move γ

K(x₁,x₂) = 0.368

Here ||x₁−x₂||² = 2, so the widget computes exp(−2γ).

6. Hyperparameters to tune

ParameterControlsPractical meaning
kernelFeature-space geometryrbf is a common nonlinear choice
nuAllowed training-error fraction / support-vector lower boundChanges how much of the training population the boundary is willing to exclude
gammaRBF localityHigh γ → more local boundary; low γ → smoother boundary
degreePolynomial degreeUsed with polynomial kernel
coef0Kernel offsetUsed with polynomial/sigmoid kernels

γ too low

Similarity changes slowly with distance. The model can become too broad and smooth.

γ too high

Similarity becomes highly local. The boundary can become intricate and sensitive to individual observations.

ν lower

You demand that a larger fraction of training observations remain inside the learned normal region.

ν higher

You permit a larger fraction of observations to lie outside the learned region.

ASM tuning workflow

  1. Build training data from a clearly defined historical, mostly-normal window.
  2. Scale numerical features because RBF distance depends on feature scale.
  3. Search a candidate grid of nu and gamma.
  4. Where confirmed anomalies exist, keep them in a temporally separate validation set.
  5. Measure precision, recall, PR-AUC, false-positive rate and alert volume.
  6. Choose parameters using the operational cost of missed anomalies versus investigation workload.
Interview answer: “For One-Class SVM, I would tune ν and γ using a mostly-clean normal training set and a temporally separate validation set containing known anomalies where available. I would monitor both detection quality and false-positive alert volume.”

7. SVM vs One-Class SVM

SVMOne-Class SVM
Training labelsNeeds class labelsPrimarily learns from normal examples
GoalSeparate classesDescribe normality / detect novelty
Typical targetBenign vs maliciousNormal vs unusual
Key parametersC, kernel, γ, degree...ν, kernel, γ, degree...
OutputClass / decision scoreInlier / outlier and decision score

8. Pocket quiz

Q1. Why does SVM minimize ||w||²?

Because the geometric margin is 2/||w||; minimizing the norm maximizes the margin.

Q2. What does γ do in an RBF kernel?

It controls how quickly similarity decreases with squared distance, affecting locality and boundary curvature.

Q3. What does One-Class SVM actually learn?

A decision region representing the training population’s normality. It does not directly learn a malicious class.

Q4. Why scale features?

RBF kernels use distances, so a feature with a much larger numeric scale can dominate the distance.

9. One-napkin summary

ORDINARY SVM labelled data ↓ separating hyperplane ↓ maximize margin ↓ y(wᵀx+b) ≥ 1 ↓ C controls violations ONE-CLASS SVM mostly-normal data ↓ feature map φ(x) ↓ learn a normality boundary ↓ wᵀφ(x) ≥ ρ − ξ ↓ ν controls allowed training-error fraction ↓ new point → normal-like / novel

Mental model: SVM draws a road between labelled populations. One-Class SVM draws a fence around a population and asks whether a new point belongs inside the learned territory.

Machine Learning Field Notebook · SVM → One-Class SVM · Study & Interview Edition
Mainak's ML Notebook: K-Means & Unsupervised Learning

K-Means Clustering & Unsupervised Learning Masterclass

Handwritten Study Notes — Complete & Unabridged

⚠️ ONE CORRECTION WORTH TATTOOING INTO YOUR ML NOTES:
KNN ≠ K-Means.
• KNN: Supervised | Has labels | Classification/Regression | "Which known neighbors look like me?"
• K-Means: Unsupervised | No labels | Clustering | "Which points naturally belong together?"

1. The Core Intuition

Think of the problem like this:

“I have data, but nobody has told me the correct group for each row. Can the algorithm discover the groups itself?”

Example Dataset (Customer Data): No target column!

CustomerAnnual SpendVisits/Year
A102
B123
C112
D7018
E7520
F7219
G3510
H4011

K-Means says: “Give me K groups, and I will find K representative centers and assign every point to its nearest center.”

Each cluster has a centroid, which is simply the mean of all points assigned to that cluster.


2. The Actual Mathematics

Suppose a point is $x = (x_1, x_2)$ and a centroid is $\mu = (\mu_1, \mu_2)$.

Euclidean Distance: $$d(x, \mu) = \sqrt{(x_1 - \mu_1)^2 + (x_2 - \mu_2)^2}$$

Numerical Example:
Point $P = (10, 2)$, Centroid $C = (12, 3)$
$d(P,C) = \sqrt{(10-12)^2 + (2-3)^2} = \sqrt{4 + 1} = \sqrt{5} = 2.236$
Nearest centroid wins!


3. What is K-Means Optimizing? (Interview Essential)

K-Means tries to minimize the Within-Cluster Sum of Squares (WCSS), commonly called inertia in scikit-learn:

$$J = \sum_{k=1}^{K} \sum_{x_i \in C_k} ||x_i - \mu_k||^2$$

In simple English: Keep points as close as possible to their cluster center. Notice distance is squared ($\sum d^2$), meaning large outliers/distances get punished heavily!


4. The K-Means Algorithm Loop

  1. Choose $K$
  2. Initialize $K$ centroids
  3. Calculate distance from every point $\to$ every centroid
  4. Assign each point to nearest centroid
  5. Calculate new centroid using mean
  6. Repeat until centroids barely move (CONVERGED)

5. Initialization Methods

Method ① — Random Initialization

Selecting initial centroids randomly from data points. Problem: If random choice picks points in the same region, K-Means can converge to a poor local minimum.

Method ② — K-Means++ (Standard Practice)

"Don't randomly put all centroids next to each other. Spread initial centroids out using weighted probabilities proportional to squared distance: $P(\text{select } x) \propto D(x)^2$."

First centroid chosen randomly. Subsequent centroids chosen with higher probability if they are farther away from already-selected centroids. This avoids bad local minima.

The n_init Parameter

n_init=10 runs K-Means 10 times with different K-Means++ initializations and keeps the solution with the lowest inertia.


6. Complete Manual Hand Calculation Example

Dataset:
Cluster 1 area: $A=(1,1), B=(2,1), C=(1,2)$
Cluster 2 area: $D=(8,8), E=(9,8), F=(8,9)$
Let $K=2$. Initial centroids: $C_1=(1,1), C_2=(8,8)$.

  • Assignment Step: Points A, B, C are closest to $C_1$. Points D, E, F are closest to $C_2$.
  • Centroid Update (Mean):
    New $C_1 = (\frac{1+2+1}{3}, \frac{1+1+2}{3}) = (1.33, 1.33)$
    New $C_2 = (\frac{8+9+8}{3}, \frac{8+8+9}{3}) = (8.33, 8.33)$
  • Repeat until convergence.

7. Interactive Visualizer Widget

Interactive K-Means Demo

Click anywhere on the canvas below to add data points, then click run to watch centroids shift!


Status: Add points and click Step!


8. Hidden Assumptions & Scaling

  • Implicit assumption: K-Means assumes clusters are compact, similar in scale, and approximately spherical/convex (Voronoi regions). It fails on interlocking or complex ring shapes.
  • Feature Scaling is Mandatory: Euclidean distance is dominated by large numbers. Always use StandardScaler() ($z = \frac{x - \mu}{\sigma}$) so features have mean=0 and std=1.
from sklearn.preprocessing import StandardScaler
from sklearn.cluster import KMeans
from sklearn.pipeline import Pipeline

pipeline = Pipeline([
    ("scaler", StandardScaler()),
    ("cluster", KMeans(n_clusters=4, init="k-means++", n_init=20, random_state=42))
])
pipeline.fit(X)
labels = pipeline.predict(X)
    

9. How to Choose K?

  1. Domain Knowledge: Business says "We need 4 customer segments" $\to K=4$.
  2. Elbow Method: Plot WCSS (Inertia) across $K = 1, 2, 3 \dots$ Look for the "elbow" where diminishing returns kick in.
  3. Silhouette Score: Measures how similar a point is to its own cluster compared to other clusters ($s \approx 1$ is ideal):
    $$s(i) = \frac{b(i) - a(i)}{\max(a(i), b(i))}$$

10. Alternative Clustering Cheat Sheet

SituationBest Algorithm
Compact numeric groupsK-Means
Unknown number of clusters / Arbitrary shapesDBSCAN / HDBSCAN
Noise and outliers important (Fraud/Anomaly)DBSCAN / HDBSCAN
Hierarchical relationships / DendrogramAgglomerative Clustering
Soft / Probabilistic membershipGaussian Mixture Model (GMM)
Very large datasets (Millions of rows)MiniBatchKMeans

11. The 80/20 Interview Map

If Interviewer Asks...Think...
Why K-Means?Compact numeric clusters, fast baseline
Objective function?Minimize SSE / WCSS (Inertia)
Why K-Means++?Smart initialization to avoid local minima
Why n_init?Run multiple times, keep lowest inertia
Why scaling?Distance-based metric prevents feature dominance
K-Means weakness?Spherical assumption, sensitive to outliers, requires K
Millions of rows?MiniBatchKMeans
- Mainak's Study Notebook 2026
ARIMA → ARIMAX → SARIMA → SARIMAX | Time Series Field Manual
Time Series Field Manual · 01

ARIMA
ARIMAX
SARIMA
SARIMAX

Four names. One family of ideas. Learn exactly what they are, why they exist, and when the extra complexity is actually useful.

PAST → PATTERNS → FUTURE

↓ Start with the problem

CHAPTER 01

First: what problem are we solving?

Suppose you have a value changing over time: sales, temperature, demand, electricity usage, website traffic, stock inventory, etc.

TIME → → → → → → → → → → → → →

The key question is not merely: "What is the average?"

The forecasting question is:

Given everything I know up to time t, what should I expect at time t + 1?
A time-series model is basically a disciplined way of saying: "Yesterday matters — but exactly HOW it matters?"
CHAPTER 02

AR — AutoRegressive

Before ARIMA, understand the simplest idea: the past value can help predict the future value.

yₜ = c + φ₁yₜ₋₁ + εₜ AR(1): today's value depends partly on yesterday's value.
AR = MEMORY

What does AR mean?

"Auto" = itself.

"Regressive" = use previous values to explain the current value.

So AR asks:

"Does my own past help predict my future?"

EXAMPLE

Temperature

Yesterday: 30°C

Today is probably not randomly 10°C.

The recent temperature contains useful information.

That is temporal memory.

Philosophy: Don't throw away yesterday. If observations are correlated over time, the previous observations contain predictive information. AR tries to capture that dependence explicitly.
CHAPTER 03

MA — Moving Average

MA does something different. Instead of directly remembering past observations, it remembers past prediction errors.

yₜ = c + εₜ + θ₁εₜ₋₁ MA(1): today's value depends on today's and yesterday's error.
MA = ERROR MEMORY

Imagine this

Your model predicted:

100

Actual value:

120

Error:

+20

The model remembers that shock when making the next prediction.

IMPORTANT

Not the normal moving average

This is where beginners get ambushed. 😄

ARIMA's MA does NOT simply mean:

"average the last 7 values."

It models past forecast errors.

CHAPTER 04

ARIMA = AR + I + MA

Now the pieces finally meet.

ARIMA(p,d,q)

p = AR order
d = differencing order
q = MA order
p

AR

How much should we look at previous observations?

d

I

How many times should we difference the series to remove trend / achieve stationarity?

q

MA

How much should we use previous forecast errors?

The "I" is the important twist

Suppose:

100 → 105 → 111 → 118 → 126

The level keeps increasing. Instead of modelling the raw values, we can model the changes:

5 → 6 → 7 → 8

That is differencing.

Δyₜ = yₜ − yₜ₋₁ First difference
ARIMA's philosophy: "If the series has a trend that makes the level unstable, model the changes rather than forcing the model to understand the trend directly."

Example

Suppose after experimentation you determine:

p = 2
d = 1
q = 1

Your model becomes:

ARIMA(2,1,1)

Meaning:

  • use 2 AR lags
  • difference once
  • use 1 past error term
CHAPTER 05

ARIMAX — ARIMA + External Information

What if the past of Y is not enough? What if another variable helps explain Y?

yₜ = c + φ₁yₜ₋₁ + β₁xₜ + εₜ X = exogenous / external variable
Y

What are we forecasting?

Example:

Ice-cream sales

X

What external variable helps?

Example:

Temperature

Why would temperature matter?

Because ice-cream demand is not caused only by yesterday's ice-cream demand.

A very hot day can cause demand to jump even if yesterday's sales were ordinary.

ARIMA: "What did Y do before?"
ARIMAX: "What did Y do before + what else is happening?"

Think like this

ARIMA = internal memory

ARIMAX = internal memory + outside information
The "X" is basically the model saying: "Hey, there are other variables in the real world. Please don't pretend they don't exist."
CHAPTER 06

Then comes the seasonal problem.

Some time series have a repeating pattern. Every day. Every week. Every month. Every year.

PEAK → FALL → LOW → RISE → PEAK → ...

For example:

  • Retail sales spike every December.
  • Electricity demand repeats every day.
  • Website traffic behaves differently on weekdays and weekends.
  • Tourism demand repeats every summer.
Seasonal period = m

Daily data with weekly seasonality → m = 7
Monthly data with yearly seasonality → m = 12
Hourly data with daily seasonality → m = 24
If ARIMA looks backward one step at a time, SARIMA also remembers the same point in the previous season.
CHAPTER 07

SARIMA — ARIMA with seasonal memory

SARIMA(p,d,q)(P,D,Q,m)
NON-SEASONAL

p, d, q

Normal ARIMA behaviour.

  • p → AR
  • d → differencing
  • q → MA
SEASONAL

P, D, Q, m

Seasonal version of the same ideas.

  • P → seasonal AR
  • D → seasonal differencing
  • Q → seasonal MA
  • m → seasonal period

Seasonal differencing

Suppose monthly sales have a yearly pattern. Compare this January with last January:

Δ₁₂ yₜ = yₜ − yₜ₋₁₂

Instead of comparing:

January 2026 − December 2025

we compare:

January 2026 − January 2025

That isolates the seasonal movement.

SARIMA(1,1,1)(1,1,1,12)

Interpretation:

  • ordinary AR order = 1
  • ordinary differencing = 1
  • ordinary MA order = 1
  • seasonal AR order = 1
  • seasonal differencing = 1
  • seasonal MA order = 1
  • season length = 12
CHAPTER 08

SARIMAX — The full family

Now combine the two extensions:

Seasonality + External Variables
SARIMAX(p,d,q)(P,D,Q,m) + X

Example: Electricity Demand

You want to forecast electricity demand.

There may be:

  • short-term dependence
  • weekly seasonality
  • temperature effects
  • holidays
  • working-day effects

So a SARIMAX model can use:

Past demand + Seasonal demand + Temperature + Holiday indicator
SARIMAX is not "better ARIMA." It is ARIMA's toolbox expanded for seasonality and external predictors.
CHAPTER 09

Interactive model selector

Answer three questions. The page will tell you which family matches the structure of your forecasting problem.

1. Does your series have a repeating seasonal pattern?


2. Do you have useful external predictors?
Choose the answers above.

Build the model name

Pick what your data needs.

MODEL CONSTRUCTOR

ARIMA

Internal temporal behaviour without seasonality or external predictors.

CHAPTER 10

The whole evolution in one picture

1. AR

"Past values of Y might predict future Y."

2. MA

"Past prediction errors might contain information."

3. ARIMA

"Combine AR + differencing + MA."

4. ARIMAX

"Also give me useful external variables."

5. SARIMA

"Also model repeating seasonal behaviour."

6. SARIMAX

"Give me seasonality AND external variables."

CHAPTER 11

One-table cheat sheet

Model Core idea Seasonality? External X? Use when...
AR Past Y predicts future Y No No Simple temporal dependence
MA Past errors influence future No No Short-lived shocks/errors matter
ARIMA AR + differencing + MA No No Trend/non-stationary series without strong seasonality
ARIMAX ARIMA + external predictors No YES Weather, price, promotions, etc. explain Y
SARIMA ARIMA + seasonal structure YES No Weekly/monthly/yearly repeating patterns
SARIMAX SARIMA + external predictors YES YES Seasonality + useful outside variables
FINAL EXAM

Interview flash cards

Click the card. Don't peek. 😄

What does ARIMA stand for?

CLICK TO REVEAL

Answer

AutoRegressive Integrated Moving Average.

AR + Differencing + MA

What is the X in ARIMAX?

CLICK TO REVEAL

Answer

Exogenous / external variables.

Variables outside the target series that may help forecast it.

What does m mean in SARIMA?

CLICK TO REVEAL

Answer

Seasonal period.

Weekly daily observations → m = 7
Yearly monthly observations → m = 12
Daily hourly observations → m = 24

THE 30-SECOND ANSWER

How to explain it in an interview

ARIMA models temporal dependence using past values, differencing, and past forecast errors.

If I also have useful external variables, I use ARIMAX.

If the series contains repeating seasonal behaviour, I use SARIMA.

If it contains both seasonality and external predictors, I use SARIMAX.

ARIMA
↓ + X
ARIMAX

ARIMA
↓ + SEASONALITY
SARIMA

SARIMA
↓ + X
SARIMAX
The model should follow the structure of the data — not the other way around.

TIME SERIES FIELD MANUAL · ARIMA FAMILY

AR → MA → ARIMA → ARIMAX → SARIMA → SARIMAX

Built as a one-page learning reference.

Sunday, September 20, 2026

SQL Server — The Vintage Field Manual
A T-SQL Field Manual · SQL Server Edition

SQL
The Vintage Field Manual

From SELECT to Window Functions — learn SQL by understanding what the database is thinking, not by memorising a pile of syntax.

SQL SERVER · T-SQL

☞ Before We Touch SQL

“SQL is not about writing commands.
It is about transforming one table into another.”

When an interviewer gives you a complicated SQL problem, do not immediately start typing.

Ask one question:

What should ONE output row represent?

One customer?

One order?

One department?

One day?

That answer determines much of the SQL that follows.

🗺 The SQL Map

flowchart TD A["SELECT
What do I want?"] B["FROM
Where is it?"] C["JOIN
Need another table?"] D["WHERE
Filter rows"] E["GROUP BY
Choose the grain"] F["AGGREGATE
COUNT / SUM / AVG"] G["HAVING
Filter groups"] H["CASE WHEN
Business rules"] I["NULL / COALESCE
Missing data"] J["CTE / SUBQUERY
Break into steps"] K["WINDOW FUNCTION
Analyse without collapsing"] L["LAG / LEAD
Previous / Next"] M["DATE FUNCTIONS"] N["STRING FUNCTIONS"] O["UNION / UNION ALL"] P["ORDER BY
Present the result"] A --> B --> C --> D --> E --> F --> G G --> H --> I --> J --> K --> L L --> M --> N --> O --> P

🧭 The Sequence

01 SELECT — choose the information
02 FROM — choose the starting table
03 DISTINCT — remove duplicate results
04 WHERE — filter rows
05 AND / OR / NOT — combine conditions
06 IN / BETWEEN / LIKE — convenient filtering
07 JOIN — connect tables
08 GROUP BY — choose analytical grain
09 COUNT / SUM / AVG / MIN / MAX — measure
10 HAVING — filter groups
11 ORDER BY — sort
12 CASE — business logic
13 NULL / COALESCE — missing values
14 SUBQUERY — nested reasoning
15 CTE — named intermediate reasoning
16 UNION — combine result sets
17 WINDOW FUNCTIONS — analyse without collapsing
18 ROW_NUMBER / RANK / DENSE_RANK — ranking
19 LAG / LEAD — compare rows
20 DATE FUNCTIONS — time analysis
21 STRING FUNCTIONS — text transformation

📜 The Example Database

Throughout this guide, imagine this tiny e-commerce database.

customers orders
customer_id
name
city
order_id
customer_id
order_date
amount
status
FIELD NOTE 01

SELECT

Question → What information do I want?

SELECT name, city
FROM customers;
Why? The database may contain dozens of columns. SELECT tells SQL which information you actually want to carry forward.

Think of it as: “Which fields belong in my answer?”
FIELD NOTE 02

FROM

Question → Where is my starting data?

SELECT *
FROM customers;
Why? Every SQL problem needs a starting point. FROM establishes the table you are currently standing on.
FIELD NOTE 03

DISTINCT

Question → Do duplicate values matter?

SELECT DISTINCT city
FROM customers;
Why? DISTINCT means: “Treat identical result values as one.” Useful when the question says “unique”. For example:
SELECT COUNT(DISTINCT hacker_id)
FROM Submissions;
means: How many unique hackers?
FIELD NOTE 04

WHERE

Question → Which individual rows should survive?

SELECT *
FROM orders
WHERE status = 'completed';
Why? WHERE is row-level filtering. Imagine 10,000 rows entering a room. WHERE says: “Only these rows may continue.”
FIELD NOTE 05

AND / OR / NOT

SELECT *
FROM orders
WHERE status = 'completed'
  AND amount > 500;
Why? Real questions contain multiple conditions. AND means: both must be true. OR means: at least one must be true. NOT reverses the condition.
FIELD NOTE 06

IN / BETWEEN / LIKE

SELECT *
FROM customers
WHERE city IN ('Kolkata','Delhi');
SELECT *
FROM orders
WHERE amount BETWEEN 500 AND 1000;
SELECT *
FROM customers
WHERE name LIKE 'A%';
Why? These are simply convenient ways of expressing common business questions.
IN → “Is it in this set?”
BETWEEN → “Is it inside this range?”
LIKE → “Does it match this pattern?”
FIELD NOTE 07

JOIN

Question → Is the information I need in another table?

SELECT
    c.name,
    o.amount
FROM customers AS c
JOIN orders AS o
    ON c.customer_id = o.customer_id;
Why? Relational databases intentionally separate information into tables. JOIN reconstructs the relationship when you need information from both. JOIN = connect related facts.
FIELD NOTE 08

LEFT JOIN

SELECT
    c.name,
    o.amount
FROM customers AS c
LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id;
Why? LEFT JOIN means: “Everyone from my left table must survive.” If a customer has no order, the order columns become NULL. The interview question to remember: “Who must survive even without a match?”
FIELD NOTE 09

GROUP BY

Question → One output row per WHAT?

SELECT
    customer_id,
    SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id;
The big idea. GROUP BY chooses the grain of your analysis. GROUP BY customer_id → one row per customer. GROUP BY city → one row per city. This is the question to ask every time you see GROUP BY:
“At what level am I analysing the data?”
FIELD NOTE 10

Aggregation

SELECT
    customer_id,
    COUNT(*) AS order_count,
    SUM(amount) AS total,
    AVG(amount) AS average,
    MIN(amount) AS minimum,
    MAX(amount) AS maximum
FROM orders
GROUP BY customer_id;
Why? GROUP BY creates the buckets. Aggregation measures what's inside them.
GROUP BY → create buckets
SUM/COUNT/AVG → measure buckets
FIELD NOTE 11

HAVING

SELECT
    customer_id,
    SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 1000;
Why? WHERE works on individual rows. HAVING works after grouping. Remember:
WHERE  → filter rows
HAVING → filter groups
FIELD NOTE 12

ORDER BY

SELECT *
FROM orders
ORDER BY amount DESC;
Why? ORDER BY doesn't change what the data means. It changes how you want to inspect the result. ASC = smallest → largest DESC = largest → smallest
FIELD NOTE 13

CASE WHEN

SELECT
    amount,
    CASE
        WHEN amount >= 1000 THEN 'HIGH'
        WHEN amount >= 500 THEN 'MEDIUM'
        ELSE 'LOW'
    END AS order_category
FROM orders;
Why? CASE is SQL's business-rule engine. Raw data: ₹700 Business meaning: MEDIUM order CASE converts raw values into business categories. SQL evaluates WHEN conditions from top to bottom.
FIELD NOTE 14

NULL

SELECT *
FROM customers
WHERE city IS NULL;

Don't write:

WHERE city = NULL;
Why? NULL represents missing or unknown information. It isn't zero. It isn't an empty string. It means: “We don't have a value here.”
FIELD NOTE 15

COALESCE

SELECT
    name,
    COALESCE(city, 'Unknown') AS city
FROM customers;
Why? COALESCE provides a fallback. “If the real value is missing, use this one.” In SQL Server, COALESCE is standard T-SQL and can accept multiple expressions.
FIELD NOTE 16

SUBQUERY

SELECT *
FROM orders
WHERE amount >
(
    SELECT AVG(amount)
    FROM orders
);
Why? The problem has two thoughts: 1. Find the average. 2. Find orders above that average. A subquery lets one query provide the answer needed by another.
FIELD NOTE 17

CTE

WITH customer_total AS
(
    SELECT
        customer_id,
        SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
)
SELECT *
FROM customer_total
WHERE total > 1000;
Why? A CTE gives a name to an intermediate result. Instead of writing one giant SQL monster: “Do everything!” you create: Step 1 → Step 2 → Step 3. This makes complicated SQL much easier to reason about.
FIELD NOTE 18

UNION

SELECT name
FROM customers_kolkata

UNION

SELECT name
FROM customers_delhi;
Use:
UNION ALL
when duplicates should remain.
Why? JOIN combines tables horizontally. UNION combines compatible result sets vertically.
JOIN
→ columns side-by-side

UNION
→ rows one-after-another
FIELD NOTE 19

WINDOW FUNCTIONS ⭐

Question → Can I analyse rows without collapsing them?

SELECT
    customer_id,
    amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
    ) AS customer_total
FROM orders;
This is the important distinction. GROUP BY:
3 Alice rows
      ↓
1 Alice row
Window function:
3 Alice rows
      ↓
3 Alice rows
+
customer total
GROUP BY collapses. WINDOW FUNCTIONS preserve.
FIELD NOTE 20

PARTITION BY

SUM(amount) OVER (
    PARTITION BY customer_id
)
Why? PARTITION BY creates a separate analytical window. Think:
Alice's rows   → Window A
Bob's rows     → Window B
Charlie        → Window C
It resembles GROUP BY logically, but rows remain visible.
FIELD NOTE 21

ROW_NUMBER

SELECT
    customer_id,
    amount,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY amount DESC
    ) AS rn
FROM orders;
Why? Within every customer, we establish an order. That gives us: 1st, 2nd, 3rd... This is the classic tool for:
  • latest record
  • top record
  • first transaction
  • deduplication
  • top-N per group
FIELD NOTE 22

RANK / DENSE_RANK

SELECT
    name,
    salary,
    RANK() OVER (
        ORDER BY salary DESC
    ) AS salary_rank
FROM employees;
For:
100
100
90
RANK:
1
1
3
DENSE_RANK:
1
1
2
ROW_NUMBER:
1
2
3
Why? The difference is simply: “What should happen when two rows tie?”
FIELD NOTE 23

LAG

SELECT
    order_date,
    amount,
    LAG(amount) OVER (
        ORDER BY order_date
    ) AS previous_amount
FROM orders;
Why? Normal SQL sees the current row. LAG lets the current row ask: “What happened immediately before me?” Useful for:
  • previous transaction
  • month-over-month comparison
  • salary changes
  • price changes
FIELD NOTE 24

LEAD

SELECT
    order_date,
    amount,
    LEAD(amount) OVER (
        ORDER BY order_date
    ) AS next_amount
FROM orders;
Why? LAG looks backward. LEAD looks forward.
LAG  ← past | current | future → LEAD
FIELD NOTE 25

ORDER BY inside OVER()

ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY order_date
)
Important. This ORDER BY does not necessarily sort the final output. It tells the window function: “What sequence should you use for the calculation?” Compare:
ORDER BY outside OVER()
→ final display order

ORDER BY inside OVER()
→ analytical sequence
FIELD NOTE 26

DATE FUNCTIONS — SQL SERVER

SELECT
    order_date,
    YEAR(order_date) AS order_year,
    MONTH(order_date) AS order_month,
    DAY(order_date) AS order_day
FROM orders;
Calculate difference:
SELECT
    DATEDIFF(day, '2026-01-01', order_date)
FROM orders;
Add time:
SELECT
    DATEADD(day, 7, order_date)
FROM orders;
Why? Dates are not just values. They are analytical dimensions: day → week → month → quarter → year. Time-based analysis is everywhere in analytics.
FIELD NOTE 27

STRING FUNCTIONS — SQL SERVER

SELECT
    UPPER(name) AS upper_name,
    LOWER(name) AS lower_name,
    LEN(name) AS name_length
FROM customers;
Combine:
SELECT
    CONCAT(name, ' - ', city) AS customer_info
FROM customers;
Why? Real-world text is messy. String functions turn raw text into useful analytical fields. Clean → transform → analyse.

⚙️ What SQL Server Logically Thinks

One of the most useful interview mental models is the logical processing order.

FROM
  ↓
JOIN
  ↓
WHERE
  ↓
GROUP BY
  ↓
HAVING
  ↓
SELECT
  ↓
DISTINCT
  ↓
ORDER BY
  ↓
TOP / OFFSET-FETCH

The written order of a SELECT statement is different from the logical processing order. This explains many SQL interview questions.

Philosophy: Don't think: “SQL reads my query from top to bottom.” Think: “SQL logically builds the result in stages.”

🧠 The Interview Pocket Card

When the interviewer says... Think...
Show me... SELECT
From this table... FROM
Only these records... WHERE
Information from another table... JOIN
One row per customer... GROUP BY customer_id
Total / average / count... SUM / AVG / COUNT
Groups above... HAVING
Classify into categories... CASE
Unique... DISTINCT
Missing... NULL / COALESCE
Previous record... LAG
Next record... LEAD
Top 1 per group... ROW_NUMBER()
Rank with ties... RANK / DENSE_RANK
Analyse but keep every row... WINDOW FUNCTION
Break complicated query into steps... CTE
Combine result sets... UNION / UNION ALL
Previous/next date... LAG / LEAD + ORDER BY

🏆 The Golden Rules

Rule #1

Before writing SQL:

What should one output row represent?

Rule #2

Ask whether your problem wants:

COLLAPSE
   ↓
GROUP BY

or

KEEP ROWS
   ↓
WINDOW

Rule #3

Remember:

WHERE
→ rows

HAVING
→ groups

Rule #4

Remember:

JOIN
→ horizontal

UNION
→ vertical

🧩 The Universal SQL Algorithm


1. What should ONE output row represent?

2. What is my starting table?

3. Do I need another table?

4. Which raw rows should survive?

5. Do I need groups?

6. What should I calculate per group?

7. Do I need to filter those groups?

8. Do I need business rules?

9. Can values be NULL?

10. Is the problem becoming multi-step?

11. Do I need to preserve rows while analysing?

12. Do I need previous/next rows?

13. Do I need ranking?

14. Do I need date transformation?

15. Do I need string transformation?

16. Do I need to combine result sets?

17. How should the final result be sorted?
The real SQL skill: You are not trying to remember 30 commands. You are decomposing a business question into a sequence of small table transformations.

☞ One Last Mental Picture


                  BUSINESS QUESTION
                         │
                         ▼
                 "What is ONE row?"
                         │
                         ▼
                       FROM
                         │
                         ▼
                       JOIN
                         │
                         ▼
                      WHERE
                         │
                         ▼
                ┌────────┴────────┐
                │                 │
             COLLAPSE          KEEP ROWS
                │                 │
                ▼                 ▼
            GROUP BY           WINDOW
                │                 │
                ▼                 ▼
           AGGREGATION       RANK / LAG
                │                 │
                └────────┬────────┘
                         ▼
                       CASE
                         │
                         ▼
                   NULL HANDLING
                         │
                         ▼
                   CTE / SUBQUERY
                         │
                         ▼
                    FINAL TABLE
                         │
                         ▼
                     ORDER BY
SQL becomes much less scary when you stop seeing syntax
and start seeing table transformations.

✦ SQL SERVER / T-SQL FIELD MANUAL ✦

Built for interview thinking, analytics, and real-world problem solving.

SELECT → FILTER → CONNECT → GROUP → CALCULATE → ANALYSE → TRANSFORM