Formulas & shortcuts

SQL12 formulas17 shortcuts

Every formula in this course and the easiest ways to remember them, on one page.

Changing data and designing databases

Advanced

Upsert and RETURNING

Open lesson

Changing a schema: ALTER TABLE and migrations

Open lesson

Advanced SQL

Advanced

Window functions

Open lesson

CTEs: WITH and recursive queries

Open lesson

CASE and conditional logic

Open lesson

String, date and math functions

Open lesson

Set operations and advanced joins

Open lesson

Programming inside the database

Advanced

Triggers

Open lesson

JSON in SQL: SQLite and PostgreSQL

Open lesson

Sequences: gaps and islands

Open lesson
grp = value − ROW_NUMBER() OVER (ORDER BY value)

Where

value
the value in the sequence (an id, a day number)
ROW_NUMBER()
the number of the sorted row: 1, 2, 3, …
grp
the island key: the same within one island

Then GROUP BY grp collapses each island into one row: MIN is the start, MAX the end, COUNT the length.

Databases at a professional level

University

Database design and normalisation

Open lesson
X → Y ⇔ ∀ t₁, t₂ ∈ r : t₁[X] = t₂[X] ⇒ t₁[Y] = t₂[Y]

Where

X
the determinant — a set of attributes
Y
the set of dependent attributes
r
any valid state of the table (relation)
t₁, t₂
any two rows of the table; t[X] — the row's values in the columns X

Two rows that agree on X must also agree on Y.

(R₁ ∩ R₂) → R₁ ∨ (R₁ ∩ R₂) → R₂

Where

R₁, R₂
the attribute sets of the two tables produced by the decomposition (R₁ ∪ R₂ = R)
R₁ ∩ R₂
the common attributes — the columns the JOIN is done on

If the condition holds, the decomposition is lossless: R = R₁ ⋈ R₂.

Transactions and concurrency

Open lesson
s = s₀ − q₂≠ s₀ − q₁ − q₂

Where

s₀
the initial stock that both transactions read
q₁, q₂
the quantities sold by T1 and T2
s
the result saved by the last writer (T2)

A lost update: in the “read → compute in the application → write” pattern, T1's sale drops out of the total.

Query optimisation and indexes

Open lesson
h = ⌈ log N ÷ log f ⌉

Where

h
the height of the tree — the number of pages read to find one key
N
the number of keys (rows) in the index
f
the fan-out — how many keys fit in one page (usually hundreds)

The height grows logarithmically with the number of rows: when the table grows 500 times, the tree gains just one level.

s = n ÷ N

Where

s
the selectivity of the condition (between 0 and 1)
n
the number of rows matching the condition
N
all rows in the table

A small s (a condition that returns few rows) is good for an index; when s is large, a full scan may be cheaper.

PostgreSQL and MySQL in practice

Open lesson

Data analysis with SQL

Open lesson
AOV = R ÷ Nₒ

Where

AOV
average order value, manat
R
revenue for the period: Σ quantity · price, manat
Nₒ
the number of orders in that period
r = A ÷ C₀ · 100%

Where

r
the cohort's retention rate
C₀
the cohort size — customers who arrived in the first period
A
customers who ordered again in a later period
cᵢ = nᵢ ÷ n₁ · 100%

Where

cᵢ
conversion up to stage i
nᵢ
customers who reached stage i
n₁
the first stage of the funnel

Step conversion is nᵢ ÷ nᵢ₋₁ · 100%; it answers the question “where do we lose the most?”.

k = ⌈ p ÷ 100 · n ⌉

Where

k
the position of the percentile in the sorted list (starting from 1)
p
the percentile, e.g. 90
n
the number of values

The nearest-rank method: the p-th percentile is the smallest value that covers at least p% of the values.

Beyond one database: warehouses and scaling

University

Data warehouses: star schema, facts and dimensions

Open lesson
average_price = Σ revenue / Σ quantityaverage_price = Σ revenue / Σ quantity

Where

Σ revenue
the sum of revenue over the selected rows
Σ quantity
the sum of units sold in those rows

The average price is weighted; AVG(unit_price) gives every row the same weight and is wrong.

date_key = year · 10000 + month · 100 + day

Where

year
the year, e.g. 2025
month
the month, 1–12
day
the day of the month, 1–31

Such a key sorts like a date and can be read by eye.