SQL — Module 43Intermediate

Math Functions

ROUND, FLOOR, CEIL, ABS, MOD, POWER, SQRT, LOG — every numeric operation for financial calculations, statistical analysis, data bucketing, and percentage computations

10–14 min April 2026

Section 9 · SQL Functions

SQL Functions · 4 modulesModule 43

String Date Math Type

// Part 01

Why Math Functions Matter in SQL

Most analytical SQL produces numbers — revenue totals, percentage shares, growth rates, margins, ratios. Raw numbers rarely arrive in the format you need: a sum might need rounding to two decimal places, a ratio needs to be expressed as a percentage, a negative difference needs its absolute value. Math functions are the final layer that transforms raw computed numbers into accurate, display-ready values.

Beyond formatting, math functions enable bucketing (grouping values into ranges), statistical summaries (standard deviation, variance), financial calculations (compound growth, loan repayment), and safely handling division-by-zero — one of the most common runtime errors in analytical SQL.

// Part 02

Rounding — ROUND, FLOOR, CEIL, TRUNC

ROUND→ rounded number

ROUND(number, decimal_places)

Rounds to specified decimal places. ROUND(4.567, 2) = 4.57. ROUND(4.564, 2) = 4.56. ROUND(4.5, 0) = 5. Negative places round to tens/hundreds: ROUND(1234, -2) = 1200.

FLOOR→ largest integer ≤ number

FLOOR(number)

Always rounds down. FLOOR(4.9) = 4. FLOOR(-4.1) = -5. Useful for bucketing into ranges.

CEIL / CEILING→ smallest integer ≥ number

CEIL(number)

Always rounds up. CEIL(4.1) = 5. CEIL(-4.9) = -4. Useful for page counts, minimum quantities.

TRUNC→ truncated number

TRUNC(number, decimal_places)

Truncates (cuts off) without rounding. TRUNC(4.99, 1) = 4.9. TRUNC(4.99, 0) = 4. Differs from ROUND — never rounds up.

Loading FreshCart DB…
-- Compare rounding behaviours on FreshCart prices
SELECT
  unit_price,
  ROUND(unit_price, 0)          AS rounded_to_int,
  ROUND(unit_price, 1)          AS rounded_to_1dp,
  ROUND(unit_price, 2)          AS rounded_to_2dp,
  FLOOR(unit_price)             AS floored,
  CEIL(unit_price)              AS ceiled,
  TRUNC(unit_price, 0)          AS truncated,
  -- Rounding to nearest 10
  ROUND(unit_price / 10) * 10   AS nearest_10
FROM products
ORDER BY unit_price DESC
LIMIT 10;Ctrl + Enter to run
Loading FreshCart database in your browser…

Loading FreshCart DB…
-- Financial rounding: revenue summary with proper decimal places
SELECT
  store_id,
  COUNT(*)                              AS orders,
  SUM(total_amount)                     AS raw_total,         -- many decimals
  ROUND(SUM(total_amount), 2)           AS revenue_2dp,       -- ₹ precision
  ROUND(AVG(total_amount), 2)           AS avg_order,
  -- Round to nearest ₹50 for display
  ROUND(SUM(total_amount) / 50) * 50   AS revenue_to_50
FROM orders
WHERE order_status = 'Delivered'
GROUP BY store_id
ORDER BY revenue_2dp DESC;Ctrl + Enter to run
Loading FreshCart database in your browser…

Banker's rounding vs standard rounding

ROUND behaviour at exactly .5

-- Standard ROUND: always rounds .5 up
ROUND(0.5, 0)  = 1
ROUND(1.5, 0)  = 2
ROUND(2.5, 0)  = 3

-- PostgreSQL uses "round half up" (same as above)
-- Some databases use "banker's rounding" (round half to even):
-- Banker's: ROUND(2.5) = 2 (rounds to nearest even)
-- Banker's: ROUND(3.5) = 4 (rounds to nearest even)

-- For financial reporting, explicitly control rounding:
-- Always specify decimal places in ROUND — never rely on default
ROUND(amount, 2)   -- always explicit for currency
-- Use FLOOR for conservative estimates, CEIL for coverage quantities

// Part 03

ABS — Absolute Value

ABS returns the absolute (non-negative) value of a number. It is essential whenever you care about the magnitude of a difference rather than its direction — how far two values differ, not which is larger.

Loading FreshCart DB…
-- Delivery time deviation from target (3 days)
SELECT
  order_id,
  store_id,
  delivery_date - order_date                            AS actual_days,
  3                                                     AS target_days,
  (delivery_date - order_date) - 3                      AS deviation,       -- signed
  ABS((delivery_date - order_date) - 3)                 AS abs_deviation,   -- magnitude
  CASE
    WHEN delivery_date - order_date <= 3 THEN 'On time'
    ELSE 'Late by ' || ((delivery_date - order_date) - 3)::TEXT || ' days'
  END                                                   AS status
FROM orders
WHERE order_status = 'Delivered'
  AND delivery_date IS NOT NULL
ORDER BY abs_deviation DESC
LIMIT 10;Ctrl + Enter to run
Loading FreshCart database in your browser…

Loading FreshCart DB…
-- Products whose price deviates most from category average
-- ABS used to find the largest deviation regardless of direction
SELECT
  p.product_name,
  p.category,
  p.unit_price,
  ROUND(cat_avg.avg_price, 2)                           AS category_avg,
  ROUND(p.unit_price - cat_avg.avg_price, 2)            AS deviation,
  ROUND(ABS(p.unit_price - cat_avg.avg_price), 2)       AS abs_deviation
FROM products AS p
JOIN (
  SELECT category, AVG(unit_price) AS avg_price
  FROM products GROUP BY category
) AS cat_avg ON p.category = cat_avg.category
ORDER BY abs_deviation DESC
LIMIT 10;Ctrl + Enter to run
Loading FreshCart database in your browser…

// Part 04

MOD — Remainder After Division

MOD returns the remainder after integer division. It is used for cycling through values, identifying even/odd rows, generating alternating patterns, and extracting the last N digits of a number.

MOD — syntax and common uses

MOD(dividend, divisor)     -- returns remainder
-- or: dividend % divisor    (DuckDB, PostgreSQL, MySQL)

MOD(10, 3)   = 1    -- 10 / 3 = 3 remainder 1
MOD(12, 4)   = 0    -- 12 / 4 = 3 remainder 0 (evenly divisible)
MOD(7, 2)    = 1    -- odd number (odd if MOD(n,2) = 1)
MOD(8, 2)    = 0    -- even number

-- Common uses:
-- Even/odd row identification: MOD(row_number, 2) = 0
-- Group cycling: MOD(customer_id, 3) assigns customers to 3 buckets (0,1,2)
-- Last N digits: MOD(order_id, 100) gives last 2 digits of order_id
-- Quarterly bucket: MOD(EXTRACT(MONTH FROM date) - 1, 3) = 0 for first month of quarter

Loading FreshCart DB…
-- MOD for A/B test bucket assignment
-- Customers split into 3 groups based on customer_id
SELECT
  customer_id,
  first_name || ' ' || last_name           AS customer,
  loyalty_tier,
  -- Assign to bucket 0, 1, or 2 based on customer_id
  MOD(customer_id, 3)                      AS ab_bucket,
  CASE MOD(customer_id, 3)
    WHEN 0 THEN 'Control'
    WHEN 1 THEN 'Treatment A'
    WHEN 2 THEN 'Treatment B'
  END                                      AS experiment_group
FROM customers
ORDER BY ab_bucket, customer_id;Ctrl + Enter to run
Loading FreshCart database in your browser…

Loading FreshCart DB…
-- MOD to identify even and odd order IDs
-- Useful for splitting datasets for validation
SELECT
  order_id,
  total_amount,
  MOD(order_id, 2)                         AS is_odd,
  CASE MOD(order_id, 2)
    WHEN 0 THEN 'Even — validation set'
    WHEN 1 THEN 'Odd — training set'
  END                                      AS dataset_split
FROM orders
WHERE order_status = 'Delivered'
ORDER BY order_id
LIMIT 10;Ctrl + Enter to run
Loading FreshCart database in your browser…

// Part 05

POWER, SQRT, CBRT — Exponents and Roots

POWER→ base^exponent

POWER(base, exponent)

POWER(2, 10) = 1024. POWER(9, 0.5) = 3 (square root). POWER(amount, 2) = amount squared. Used for compound growth, geometric means.

SQRT→ square root

SQRT(number)

SQRT(25) = 5. SQRT(2) = 1.4142... Returns NULL for negative inputs in most databases. Used for standard deviation calculations.

CBRT→ cube root

CBRT(number)

CBRT(27) = 3. CBRT(8) = 2. Available in PostgreSQL and DuckDB. Used in volume calculations.

EXP→ e^number

EXP(number)

EXP(1) = 2.71828 (Euler's number). EXP(0) = 1. Used for exponential growth models and log-scale transformations.

Loading FreshCart DB…
-- Compound Annual Growth Rate (CAGR) calculation
-- CAGR = (end_value / start_value)^(1/years) - 1
-- Using Jan vs Feb revenue as a simplified example
WITH monthly_rev AS (
  SELECT
    EXTRACT(MONTH FROM order_date)::INT   AS mo,
    ROUND(SUM(total_amount), 2)           AS revenue
  FROM orders
  WHERE order_status = 'Delivered'
    AND EXTRACT(YEAR FROM order_date) = 2024
  GROUP BY EXTRACT(MONTH FROM order_date)
)
SELECT
  jan.revenue                             AS jan_revenue,
  feb.revenue                             AS feb_revenue,
  -- Month-over-month growth rate
  ROUND((feb.revenue - jan.revenue) / jan.revenue * 100, 2) AS mom_pct,
  -- Annualised from 1-month growth: (1 + monthly_rate)^12 - 1
  ROUND(
    (POWER(1 + (feb.revenue - jan.revenue) / jan.revenue, 12) - 1) * 100
  , 2)                                    AS annualised_growth_pct
FROM monthly_rev AS jan, monthly_rev AS feb
WHERE jan.mo = 1 AND feb.mo = 2;Ctrl + Enter to run
Loading FreshCart database in your browser…

Loading FreshCart DB…
-- SQRT in statistical calculation: standard deviation manually
-- stddev = SQRT(AVG((x - mean)^2))
WITH order_stats AS (
  SELECT
    store_id,
    total_amount,
    AVG(total_amount) OVER (PARTITION BY store_id) AS store_avg
  FROM orders
  WHERE order_status = 'Delivered'
)
SELECT
  store_id,
  ROUND(AVG(total_amount), 2)                           AS avg_order,
  -- Manual stddev using SQRT and POWER:
  ROUND(SQRT(AVG(POWER(total_amount - store_avg, 2))), 2) AS manual_stddev,
  -- Built-in for comparison:
  ROUND(STDDEV(total_amount), 2)                        AS builtin_stddev
FROM order_stats
GROUP BY store_id
ORDER BY store_id;Ctrl + Enter to run
Loading FreshCart database in your browser…

// Part 06

LOG and LN — Logarithms

Logarithm functions

-- LN: natural logarithm (base e)
LN(1)           = 0
LN(EXP(1))      = 1      -- LN and EXP are inverses
LN(100)         = 4.605...

-- LOG: base-10 logarithm (or arbitrary base)
LOG(100)        = 2      -- base 10
LOG(10, 100)    = 2      -- LOG(base, number) — explicit base
LOG(2, 1024)    = 10     -- log base 2 of 1024

-- DuckDB: LOG10() for base-10, LOG2() for base-2
LOG10(1000)     = 3
LOG2(1024)      = 10

-- Common uses:
-- Log-scale buckets: FLOOR(LOG10(amount)) puts amounts into order-of-magnitude buckets
-- Revenue distribution: amounts in ₹10s, ₹100s, ₹1000s in separate buckets
-- Growth rates: continuous compounding uses LN
-- Normalising skewed distributions for analysis

Loading FreshCart DB…
-- Log-scale order value buckets
-- Groups orders into order-of-magnitude ranges
SELECT
  CASE
    WHEN total_amount < 100   THEN '₹10-99'
    WHEN total_amount < 1000  THEN '₹100-999'
    WHEN total_amount < 10000 THEN '₹1,000-9,999'
    ELSE                           '₹10,000+'
  END                                       AS value_bucket,
  -- Floor of log10 gives the order of magnitude
  FLOOR(LOG10(total_amount))::INT           AS magnitude,
  COUNT(*)                                  AS order_count,
  ROUND(AVG(total_amount), 2)               AS avg_in_bucket,
  ROUND(SUM(total_amount), 2)               AS total_in_bucket
FROM orders
WHERE order_status = 'Delivered'
  AND total_amount > 0
GROUP BY
  CASE
    WHEN total_amount < 100   THEN '₹10-99'
    WHEN total_amount < 1000  THEN '₹100-999'
    WHEN total_amount < 10000 THEN '₹1,000-9,999'
    ELSE                           '₹10,000+'
  END,
  FLOOR(LOG10(total_amount))::INT
ORDER BY magnitude;Ctrl + Enter to run
Loading FreshCart database in your browser…

// Part 07

Division and NULLIF — Preventing Division by Zero

Division by zero is one of the most common runtime errors in SQL — it crashes the query with no data returned. The fix is NULLIF: it returns NULL when its two arguments are equal, and NULL divided by anything is NULL (not an error). Wrapping the denominator with NULLIF(denominator, 0) silently converts zero-division attempts into NULL results.

Division by zero — the NULLIF pattern

-- Division by zero → ERROR
SELECT 100 / 0;                          -- ERROR: division by zero

-- NULLIF converts zero to NULL — NULL / anything = NULL (no error)
SELECT 100 / NULLIF(0, 0);              -- returns NULL
SELECT 100 / NULLIF(total_orders, 0);  -- safe even when total_orders = 0

-- Why NULLIF works:
-- NULLIF(a, b) returns NULL when a = b, otherwise returns a
-- So NULLIF(0, 0) = NULL, and 100 / NULL = NULL (not an error)
-- NULLIF(5, 0) = 5 (unchanged), and 100 / 5 = 20 (normal division)

-- Combine with COALESCE to substitute 0 instead of NULL:
COALESCE(revenue / NULLIF(orders, 0), 0)   -- returns 0 when orders = 0

-- Common patterns:
-- Percentage: ROUND(part / NULLIF(total, 0) * 100, 1)
-- Ratio:      ROUND(metric_a / NULLIF(metric_b, 0), 2)
-- Growth:     ROUND((new - old) / NULLIF(old, 0) * 100, 1)

Loading FreshCart DB…
-- Percentage calculations with NULLIF protection
SELECT
  p.category,
  COUNT(*)                                                    AS product_count,
  SUM(CASE WHEN p.in_stock THEN 1 ELSE 0 END)                AS in_stock_count,
  -- Percentage in stock — NULLIF protects against empty categories
  ROUND(
    SUM(CASE WHEN p.in_stock THEN 1 ELSE 0 END)::NUMERIC
    / NULLIF(COUNT(*), 0) * 100
  , 1)                                                        AS pct_in_stock,
  ROUND(AVG(unit_price), 2)                                   AS avg_price,
  -- Margin percentage
  ROUND(
    AVG((unit_price - cost_price) / NULLIF(unit_price, 0) * 100)
  , 1)                                                        AS avg_margin_pct
FROM products AS p
GROUP BY p.category
ORDER BY avg_margin_pct DESC;Ctrl + Enter to run
Loading FreshCart database in your browser…

Loading FreshCart DB…
-- Revenue per employee — NULLIF prevents crash when store has 0 employees
WITH store_revenue AS (
  SELECT store_id, ROUND(SUM(total_amount), 2) AS revenue
  FROM orders WHERE order_status = 'Delivered'
  GROUP BY store_id
),
store_headcount AS (
  SELECT store_id, COUNT(*) AS headcount
  FROM employees WHERE store_id IS NOT NULL
  GROUP BY store_id
)
SELECT
  s.store_id,
  s.city,
  COALESCE(r.revenue, 0)                                   AS revenue,
  COALESCE(h.headcount, 0)                                 AS employees,
  -- NULLIF prevents division by zero for stores with no employees
  ROUND(
    COALESCE(r.revenue, 0) / NULLIF(COALESCE(h.headcount, 0), 0)
  , 2)                                                     AS revenue_per_employee
FROM stores AS s
LEFT JOIN store_revenue   AS r ON s.store_id = r.store_id
LEFT JOIN store_headcount AS h ON s.store_id = h.store_id
ORDER BY revenue_per_employee DESC NULLS LAST;Ctrl + Enter to run
Loading FreshCart database in your browser…

// Part 08

Statistical Aggregates — STDDEV, VARIANCE, PERCENTILE

SQL includes statistical aggregate functions that go beyond SUM, AVG, and COUNT. These functions are essential for understanding data distributions — not just the average, but how spread out values are and what the typical range looks like.

Statistical aggregate functions

-- Standard deviation: spread around the mean
STDDEV(column)          -- sample standard deviation (divides by n-1)
STDDEV_POP(column)      -- population standard deviation (divides by n)

-- Variance: squared standard deviation
VARIANCE(column)        -- sample variance
VAR_POP(column)         -- population variance

-- Percentiles: value at which N% of data falls below
PERCENTILE_CONT(0.5)  WITHIN GROUP (ORDER BY column)   -- median (50th percentile)
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY column)   -- 25th percentile (Q1)
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY column)   -- 75th percentile (Q3)
PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY column)   -- 95th percentile

-- PERCENTILE_DISC: returns an actual value from the dataset
-- PERCENTILE_CONT: interpolates between values (exact percentile)

-- CORR: Pearson correlation coefficient (-1 to 1)
CORR(col_a, col_b)      -- 1 = perfect positive, -1 = perfect negative, 0 = no linear relationship

Loading FreshCart DB…
-- Distribution analysis of order values
SELECT
  ROUND(MIN(total_amount), 2)                                  AS minimum,
  ROUND(PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY total_amount), 2) AS p25,
  ROUND(PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY total_amount), 2) AS median,
  ROUND(AVG(total_amount), 2)                                  AS mean,
  ROUND(PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY total_amount), 2) AS p75,
  ROUND(PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY total_amount), 2) AS p95,
  ROUND(MAX(total_amount), 2)                                  AS maximum,
  ROUND(STDDEV(total_amount), 2)                               AS std_dev,
  -- Interquartile range (IQR)
  ROUND(
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY total_amount)
    - PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY total_amount)
  , 2)                                                         AS iqr
FROM orders
WHERE order_status = 'Delivered';Ctrl + Enter to run
Loading FreshCart database in your browser…

Loading FreshCart DB…
-- Per-store distribution: mean, stddev, and coefficient of variation
-- Coefficient of variation = stddev / mean — measures relative spread
SELECT
  store_id,
  COUNT(*)                              AS orders,
  ROUND(AVG(total_amount), 2)           AS mean_order,
  ROUND(STDDEV(total_amount), 2)        AS std_dev,
  ROUND(MIN(total_amount), 2)           AS minimum,
  ROUND(MAX(total_amount), 2)           AS maximum,
  -- CV: lower = more consistent order values, higher = more variable
  ROUND(
    STDDEV(total_amount) / NULLIF(AVG(total_amount), 0) * 100
  , 1)                                  AS coeff_variation_pct
FROM orders
WHERE order_status = 'Delivered'
GROUP BY store_id
ORDER BY coeff_variation_pct;Ctrl + Enter to run
Loading FreshCart database in your browser…

Loading FreshCart DB…
-- Outlier detection: orders beyond 2 standard deviations from mean
WITH order_stats AS (
  SELECT
    AVG(total_amount)    AS mean_val,
    STDDEV(total_amount) AS std_val
  FROM orders WHERE order_status = 'Delivered'
)
SELECT
  o.order_id,
  o.store_id,
  o.total_amount,
  ROUND(os.mean_val, 2)                                    AS overall_mean,
  ROUND(os.std_val, 2)                                     AS overall_stddev,
  ROUND(ABS(o.total_amount - os.mean_val) / NULLIF(os.std_val, 0), 2) AS z_score,
  CASE
    WHEN ABS(o.total_amount - os.mean_val) > 2 * os.std_val THEN '⚠ Outlier'
    ELSE '✓ Normal'
  END                                                      AS classification
FROM orders AS o, order_stats AS os
WHERE o.order_status = 'Delivered'
ORDER BY z_score DESC
LIMIT 10;Ctrl + Enter to run
Loading FreshCart database in your browser…

// Part 09

Numeric Bucketing — WIDTH_BUCKET and CASE Ranges

Bucketing assigns each row to a numeric range — turning continuous values into discrete categories for distribution analysis and histograms. SQL provides WIDTH_BUCKET for uniform-width buckets and CASE for custom ranges.

Loading FreshCart DB…
-- WIDTH_BUCKET: uniform-width buckets
-- WIDTH_BUCKET(value, min, max, num_buckets)
-- Returns bucket number 1 through num_buckets
SELECT
  WIDTH_BUCKET(total_amount, 0, 2000, 10)   AS bucket_num,
  -- Bucket label (each bucket covers ₹200)
  (WIDTH_BUCKET(total_amount, 0, 2000, 10) - 1) * 200 AS bucket_min,
  WIDTH_BUCKET(total_amount, 0, 2000, 10) * 200       AS bucket_max,
  COUNT(*)                                            AS order_count,
  ROUND(AVG(total_amount), 2)                         AS avg_in_bucket
FROM orders
WHERE order_status = 'Delivered'
  AND total_amount BETWEEN 0 AND 2000
GROUP BY WIDTH_BUCKET(total_amount, 0, 2000, 10)
ORDER BY bucket_num;Ctrl + Enter to run
Loading FreshCart database in your browser…

Loading FreshCart DB…
-- Custom CASE bucketing: business-meaningful price tiers
SELECT
  CASE
    WHEN unit_price < 50   THEN '₹0–49 (Budget)'
    WHEN unit_price < 150  THEN '₹50–149 (Standard)'
    WHEN unit_price < 300  THEN '₹150–299 (Premium)'
    WHEN unit_price < 500  THEN '₹300–499 (Luxury)'
    ELSE                        '₹500+ (Ultra)'
  END                            AS price_tier,
  COUNT(*)                       AS product_count,
  ROUND(AVG(unit_price), 2)      AS avg_price,
  ROUND(MIN(unit_price), 2)      AS min_price,
  ROUND(MAX(unit_price), 2)      AS max_price
FROM products
GROUP BY
  CASE
    WHEN unit_price < 50   THEN '₹0–49 (Budget)'
    WHEN unit_price < 150  THEN '₹50–149 (Standard)'
    WHEN unit_price < 300  THEN '₹150–299 (Premium)'
    WHEN unit_price < 500  THEN '₹300–499 (Luxury)'
    ELSE                        '₹500+ (Ultra)'
  END
ORDER BY min_price;Ctrl + Enter to run
Loading FreshCart database in your browser…

// Part 10

Integer Division and Type Casting

SQL type rules for division are a frequent source of bugs. When both operands are integers, SQL performs integer division — discarding the remainder. 7 / 2 = 3, not 3.5. Casting one operand to NUMERIC or FLOAT forces decimal division.

Integer division trap and fix

-- INTEGER division: both operands are integers
SELECT 7 / 2;               -- returns 3 (not 3.5!)
SELECT 1 / 3;               -- returns 0 (not 0.333!)
SELECT 10 / 3;              -- returns 3 (remainder discarded)

-- Fix 1: cast one operand to NUMERIC or FLOAT
SELECT 7::NUMERIC / 2;      -- returns 3.5
SELECT 7 / 2.0;             -- returns 3.5 (2.0 is a float literal)
SELECT CAST(7 AS NUMERIC) / 2;  -- returns 3.5

-- Fix 2: multiply by 1.0 first
SELECT 7 * 1.0 / 2;        -- returns 3.5

-- Common mistake: percentage calculation on integer counts
SELECT COUNT(*) / COUNT(*) * 100 FROM orders;       -- returns 100 (integer: 1 * 100)
SELECT COUNT(*)::NUMERIC / COUNT(*) * 100 FROM orders;  -- returns 100.0 (correct)

-- Percentage of delivered orders:
SELECT
  SUM(CASE WHEN order_status = 'Delivered' THEN 1 ELSE 0 END)::NUMERIC
  / COUNT(*) * 100   -- cast numerator to NUMERIC → decimal result
FROM orders;

Loading FreshCart DB…
-- Demonstrate integer division trap and fix
SELECT
  -- WRONG: integer division
  3 / 4                              AS int_div_wrong,      -- 0
  -- CORRECT: cast to numeric
  3::NUMERIC / 4                     AS numeric_div,        -- 0.75
  -- Percentage: delivered orders
  SUM(CASE WHEN order_status = 'Delivered' THEN 1 ELSE 0 END)
    AS delivered_count,
  COUNT(*)
    AS total_count,
  -- WRONG: integer / integer * 100
  SUM(CASE WHEN order_status = 'Delivered' THEN 1 ELSE 0 END)
    / COUNT(*) * 100                 AS wrong_pct,          -- always 0 or 100!
  -- CORRECT: cast numerator
  ROUND(
    SUM(CASE WHEN order_status = 'Delivered' THEN 1 ELSE 0 END)::NUMERIC
    / COUNT(*) * 100
  , 1)                               AS correct_pct
FROM orders;Ctrl + Enter to run
Loading FreshCart database in your browser…

// Part 11

Complete Math Function Reference

Function	Description	Example → Result
ROUND(n, dp)	Round to dp decimal places	ROUND(3.456, 2) → 3.46
FLOOR(n)	Largest integer ≤ n	FLOOR(3.9) → 3
CEIL(n)	Smallest integer ≥ n	CEIL(3.1) → 4
TRUNC(n, dp)	Truncate to dp decimal places	TRUNC(3.99, 1) → 3.9
ABS(n)	Absolute value	ABS(-42) → 42
MOD(a, b)	Remainder of a / b	MOD(10, 3) → 1
POWER(b, e)	b raised to exponent e	POWER(2, 8) → 256
SQRT(n)	Square root	SQRT(144) → 12
CBRT(n)	Cube root	CBRT(27) → 3
EXP(n)	e raised to power n	EXP(1) → 2.71828
LN(n)	Natural logarithm (base e)	LN(1) → 0
LOG(n)	Base-10 logarithm	LOG(1000) → 3
LOG(b, n)	Logarithm base b of n	LOG(2, 1024) → 10
SIGN(n)	Sign of n: -1, 0, or 1	SIGN(-42) → -1
PI()	Value of π	PI() → 3.14159...
RANDOM()	Random float 0 ≤ x < 1	RANDOM() → 0.73421...
WIDTH_BUCKET(v, lo, hi, n)	Uniform bucket number for v	WIDTH_BUCKET(350, 0, 1000, 10) → 4
NULLIF(a, b)	NULL if a=b, else a	NULLIF(0, 0) → NULL
STDDEV(col)	Sample standard deviation	STDDEV(salary) → 12450.3
VARIANCE(col)	Sample variance	VARIANCE(salary) → 155010000
PERCENTILE_CONT(f) WITHIN GROUP (ORDER BY col)	Interpolated percentile	PERCENTILE_CONT(0.5) → median
CORR(a, b)	Pearson correlation coefficient	CORR(price, sales) → -0.72

// Part 12

What This Looks Like at Work

You are a data analyst at Brex, a fintech platform. The finance team needs a monthly P&L summary showing revenue, cost of goods, gross margin, gross margin percentage, standard deviation of transaction values, and identification of outlier transactions. All require combinations of math functions.

10:00 AM

P&L summary requirements

Revenue, COGS, gross profit, gross margin %, avg transaction, stddev, outlier flag (beyond 2σ). All rounded to 2 dp, percentages to 1 dp.

Loading FreshCart DB…
-- Monthly P&L summary using math functions
WITH monthly_stats AS (
  SELECT
    DATE_TRUNC('month', o.order_date)::DATE           AS month_start,
    -- Revenue
    ROUND(SUM(o.total_amount), 2)                     AS revenue,
    -- COGS (sum of cost_price × quantity)
    ROUND(SUM(p.cost_price * oi.quantity), 2)         AS cogs,
    -- Gross profit
    ROUND(SUM(o.total_amount) - SUM(p.cost_price * oi.quantity), 2) AS gross_profit,
    -- Transaction stats
    COUNT(DISTINCT o.order_id)                        AS order_count,
    ROUND(AVG(o.total_amount), 2)                     AS avg_order,
    ROUND(STDDEV(o.total_amount), 2)                  AS stddev_order,
    ROUND(MIN(o.total_amount), 2)                     AS min_order,
    ROUND(MAX(o.total_amount), 2)                     AS max_order
  FROM orders      AS o
  JOIN order_items AS oi ON o.order_id    = oi.order_id
  JOIN products    AS p  ON oi.product_id = p.product_id
  WHERE o.order_status = 'Delivered'
  GROUP BY DATE_TRUNC('month', o.order_date)
)
SELECT
  month_start,
  revenue,
  cogs,
  gross_profit,
  -- Gross margin percentage — NULLIF prevents division by zero
  ROUND(gross_profit / NULLIF(revenue, 0) * 100, 1)  AS gross_margin_pct,
  order_count,
  avg_order,
  stddev_order,
  min_order,
  max_order,
  -- Upper outlier threshold: mean + 2 × stddev
  ROUND(avg_order + 2 * stddev_order, 2)             AS outlier_threshold
FROM monthly_stats
ORDER BY month_start;Ctrl + Enter to run
Loading FreshCart database in your browser…

10:45 AM

Outlier orders flagged separately

A second query identifies specific orders exceeding the monthly outlier threshold.

Loading FreshCart DB…
-- Flag individual outlier orders beyond 2 standard deviations
WITH monthly_benchmarks AS (
  SELECT
    DATE_TRUNC('month', order_date)::DATE  AS month_start,
    AVG(total_amount)                      AS mean_val,
    STDDEV(total_amount)                   AS std_val
  FROM orders WHERE order_status = 'Delivered'
  GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
  o.order_id,
  DATE_TRUNC('month', o.order_date)::DATE   AS month,
  o.store_id,
  o.total_amount,
  ROUND(mb.mean_val, 2)                     AS month_mean,
  ROUND(mb.std_val, 2)                      AS month_stddev,
  -- Z-score: how many stddevs from the mean
  ROUND(
    ABS(o.total_amount - mb.mean_val) / NULLIF(mb.std_val, 0)
  , 2)                                      AS z_score,
  CASE
    WHEN ABS(o.total_amount - mb.mean_val) > 2 * mb.std_val
    THEN '⚠ Outlier'
    ELSE '✓ Normal'
  END                                       AS flag
FROM orders AS o
JOIN monthly_benchmarks AS mb
  ON DATE_TRUNC('month', o.order_date)::DATE = mb.month_start
WHERE o.order_status = 'Delivered'
ORDER BY z_score DESC
LIMIT 8;Ctrl + Enter to run
Loading FreshCart database in your browser…

11:15 AM

Finance team gets complete P&L + outlier report

Two queries — one for the summary P&L with margin calculations, one for flagging outlier transactions. All numbers properly rounded, all divisions protected with NULLIF, all percentages cast to NUMERIC before division. The finance team has actionable numbers with statistical context.

🎯 Pro Tip

Always pair margin and percentage calculations with NULLIF on the denominator. In a P&L query, if a store has zero revenue (no delivered orders), dividing gross_profit / 0 crashes the entire query — not just that store's row. NULLIF converts the zero to NULL, the division returns NULL, and the query continues for all other stores. Add COALESCE if you want 0% instead of NULL for display.

// Part 13

Interview Prep — 5 Questions With Complete Answers

Q: What is NULLIF and when do you use it in SQL?

NULLIF(a, b) returns NULL when a equals b, and returns a unchanged when a does not equal b. Its primary use is preventing division-by-zero errors — NULLIF(denominator, 0) converts zero denominators to NULL, and NULL divided by anything is NULL (not a runtime error). Without this guard, a single row with a zero denominator crashes the entire query.

The pattern appears in every percentage and ratio calculation: ROUND(part::NUMERIC / NULLIF(total, 0) * 100, 1). For a revenue-per-order metric: ROUND(total_revenue / NULLIF(order_count, 0), 2). For growth rates: ROUND((new_val - old_val) / NULLIF(old_val, 0) * 100, 1). Any time you divide by an aggregate or by a value that could be zero in some rows, wrap the denominator in NULLIF.

Combine NULLIF with COALESCE for display purposes: COALESCE(value / NULLIF(denominator, 0), 0) returns 0 instead of NULL when the denominator is zero — useful when a zero percentage is a meaningful display value rather than a missing value. NULLIF also has a secondary use: returning NULL to suppress a specific value in aggregates — AVG(NULLIF(score, -1)) computes the average of scores while treating -1 as "not applicable" (NULLs are excluded from AVG).

Q: What is the difference between ROUND, FLOOR, CEIL, and TRUNC?

All four reduce a decimal number to fewer decimal places but with different rounding behaviour. ROUND(n, dp) rounds to the nearest value at dp decimal places — values at exactly .5 round up in standard SQL. ROUND(3.456, 2) = 3.46 (rounds up). ROUND(3.454, 2) = 3.45 (rounds down). ROUND accepts negative dp to round to tens, hundreds: ROUND(1234, -2) = 1200.

FLOOR(n) always rounds down to the largest integer not greater than n. FLOOR(3.9) = 3. FLOOR(-3.1) = -4 (more negative, not -3). CEIL(n) always rounds up to the smallest integer not less than n. CEIL(3.1) = 4. CEIL(-3.9) = -3 (less negative, not -4). TRUNC(n, dp) cuts off at dp decimal places without rounding — TRUNC(3.99, 1) = 3.9 (not 4.0). TRUNC always truncates toward zero for both positive and negative numbers.

When to use each: ROUND for financial display where standard rounding is expected (₹ amounts, percentages). FLOOR for conservative estimates, bin lower-bounds, and page-down calculations (how many complete pages?). CEIL for coverage calculations, minimum quantities, and page-up calculations (how many pages needed to fit N items?). TRUNC when you need to cut precision without any rounding — for example, extracting the integer part of a number without rounding it up: TRUNC(salary / 1000, 0) gives the thousands-level band (₹45,678 → 45).

Q: How do you calculate percentage of total in SQL?

Three approaches. First, using a subquery for the total: SELECT store_id, revenue, ROUND(revenue::NUMERIC / (SELECT SUM(revenue) FROM store_revenues) * 100, 1) AS pct_of_total FROM store_revenues. The subquery computes the grand total once and each row divides its revenue by it. NULLIF protects against a zero total. The ::NUMERIC cast prevents integer division.

Second, using a window function — more efficient and cleaner: SELECT store_id, revenue, ROUND(revenue::NUMERIC / SUM(revenue) OVER () * 100, 1) AS pct_of_total FROM store_revenues. SUM(revenue) OVER () computes the grand total across all rows and attaches it to each row in one pass — no subquery needed.

Third, using CROSS JOIN to a single-row total: WITH totals AS (SELECT SUM(revenue) AS grand_total FROM store_revenues) SELECT sr.store_id, sr.revenue, ROUND(sr.revenue / NULLIF(t.grand_total, 0) * 100, 1) AS pct FROM store_revenues AS sr CROSS JOIN totals AS t. This is explicit and readable. Key rules for all three: always cast the numerator or denominator to NUMERIC before dividing (integer / integer discards the decimal part, giving 0 or 1 for values under 100%); always protect the denominator with NULLIF(total, 0); always ROUND to a reasonable number of decimal places (1 or 2 for percentages).

Q: What is the integer division trap and how do you avoid it?

When both operands in a division are integer types, SQL performs integer division — discarding the fractional remainder. 7 / 2 = 3 (not 3.5). 1 / 3 = 0 (not 0.333). This is not a bug — it is the defined behaviour for integer arithmetic in SQL. But it produces incorrect results in percentage and ratio calculations where a decimal result is expected.

The most common symptom: a percentage calculation always returns 0 or 100 instead of a decimal value. SELECT COUNT(CASE WHEN status = 'Delivered' THEN 1 END) / COUNT(*) * 100 FROM orders — if the counts are both integers, the division produces 0 or 1 (integer), then multiplied by 100 gives 0 or 100. Never the expected decimal percentage.

Three fixes: cast one operand to NUMERIC — COUNT(...)::NUMERIC / COUNT(*) * 100; use a decimal literal — COUNT(...) / COUNT(*) * 100.0 (the 100.0 float forces decimal promotion); or use CAST explicitly — CAST(COUNT(...) AS NUMERIC) / COUNT(*) * 100. The cast must apply to the numerator or denominator before the division occurs — casting the result of an integer division (SELECT (7 / 2)::NUMERIC) still gives 3.0, not 3.5. The cast must come before the division: SELECT 7::NUMERIC / 2 → 3.5. The same trap applies to AVG of integers — AVG(integer_column) returns an integer in some databases (MySQL) but numeric in others (PostgreSQL). Always verify AVG results when working with integer columns across different databases.

Q: How do you detect outliers in SQL using standard deviation?

The standard z-score approach: a value is an outlier if it is more than N standard deviations from the mean (commonly N=2 or N=3). The z-score formula is z = (value - mean) / stddev. Values with |z| > 2 are outside 95% of the distribution (assuming normal distribution); |z| > 3 are outside 99.7%.

SQL implementation: compute mean and stddev in a CTE or derived table, then JOIN to the original table and compute z-scores per row: WITH stats AS (SELECT AVG(total_amount) AS mean_val, STDDEV(total_amount) AS std_val FROM orders WHERE order_status = 'Delivered') SELECT order_id, total_amount, ROUND(ABS(total_amount - s.mean_val) / NULLIF(s.std_val, 0), 2) AS z_score FROM orders, stats AS s WHERE ABS(total_amount - s.mean_val) > 2 * s.std_val. NULLIF protects against the case where all values are identical (stddev = 0).

An alternative to z-score is the IQR (interquartile range) method — more robust to extreme outliers: outliers are values below Q1 - 1.5 × IQR or above Q3 + 1.5 × IQR. IQR = Q3 - Q1 computed with PERCENTILE_CONT. The IQR method does not assume a normal distribution and is preferred for skewed data (like revenue or order values which are typically right-skewed). For time-series data, per-period outlier detection (z-score within each month) is more meaningful than a global z-score across all periods — a ₹5,000 order might be normal in December but an outlier in January.

// Part 14

Errors You Will Hit — And Exactly Why They Happen

ERROR: division by zero

Cause: A division expression has a denominator that evaluates to zero for at least one row. This terminates the entire query — no rows are returned at all. Common causes: dividing by COUNT() when a group has zero rows (impossible — COUNT always returns at least 0), dividing by SUM() when a group's sum is zero, dividing by a column value that contains zero, or computing a percentage where the denominator column has zero for some categories.

Fix: Wrap every denominator in NULLIF(denominator, 0). This converts zero to NULL — NULL divided by anything returns NULL rather than causing an error. ROUND(numerator / NULLIF(denominator, 0), 2) safely returns NULL for zero-denominator rows while computing normally for all other rows. Chain with COALESCE if you need 0 instead of NULL: COALESCE(numerator / NULLIF(denominator, 0), 0). Make NULLIF the default for all ratio and percentage calculations — treat it as mandatory syntax, not an edge-case protection.

Percentage calculation always returns 0 or 100 — never a decimal value

Cause: Integer division is discarding the fractional part. When both the numerator and denominator are integer types (which COUNT(*) and SUM of INTEGER columns produce), SQL performs integer division: 3 / 10 = 0, 10 / 10 = 1. Multiplying by 100 afterward gives 0 or 100. The cast to decimal must happen before the division, not after.

Fix: Cast the numerator to NUMERIC before dividing: COUNT(CASE WHEN ... THEN 1 END)::NUMERIC / COUNT(*) * 100. Or use a decimal literal in the multiplication: / COUNT(*) * 100.0. Check the types of your columns — if the column is INTEGER, SUM produces INTEGER. Cast appropriately: SUM(integer_col)::NUMERIC. Verify the fix by running SELECT 1::NUMERIC / 3 — should return 0.333..., not 0.

ROUND returns unexpected results — rounding seems wrong

Cause: Two possible causes: (1) the number being rounded is a floating-point type (FLOAT/DOUBLE) which has binary precision issues — 0.1 in binary floating point is not exactly 0.1, causing ROUND(2.675, 2) to sometimes return 2.67 instead of 2.68. (2) Banker's rounding — some databases implement round-half-to-even rather than round-half-up, so ROUND(2.5, 0) = 2 (not 3) in these implementations.

Fix: Use NUMERIC (exact decimal arithmetic) instead of FLOAT for monetary values: ROUND(amount::NUMERIC, 2). NUMERIC stores exact decimal values, avoiding binary floating-point imprecision. For round-half-up behaviour in all cases: FLOOR(value * 10^dp + 0.5) / 10^dp — this manually implements round-half-up. For financial calculations, always use NUMERIC columns and ROUND to 2 decimal places at every aggregation step — do not let floating-point accumulate through multiple operations.

STDDEV returns NULL — expected a number

Cause: STDDEV returns NULL when the input has only one row (standard deviation requires at least two values — it divides by n-1) or when all input values are NULL. STDDEV_POP divides by n and returns 0 for a single-row input, but STDDEV (sample standard deviation) returns NULL for one row because the denominator n-1 = 0.

Fix: Check the group size before computing STDDEV: CASE WHEN COUNT(*) > 1 THEN STDDEV(column) ELSE NULL END. Or use STDDEV_POP if population standard deviation is appropriate for your use case — it returns 0 for a single-row group. For outlier detection, handle the NULL with COALESCE: COALESCE(STDDEV(column), 0) treats single-row groups as having zero spread. Also verify no upstream NULL filtering has reduced the group to one row — a WHERE clause that is too restrictive can produce single-row groups where multi-row groups are expected.

POWER(base, 0.5) returns wrong result for negative base

Cause: SQRT and POWER with fractional exponents return NULL (or an error) for negative inputs in most SQL databases. POWER(-4, 0.5) is mathematically a complex number — SQL does not support complex arithmetic and returns NULL or raises an error. This can occur when computing distance or RMS values on data that contains negative differences.

Fix: Ensure the base is non-negative before calling POWER or SQRT. For standard deviation-related calculations use ABS first: SQRT(ABS(variance_value)). For distance calculations: SQRT(POWER(ABS(x2 - x1), 2) + POWER(ABS(y2 - y1), 2)) — ABS is technically unnecessary for squared differences (squaring makes all values positive) but documents the intent. Use NULLIF to handle edge cases: SQRT(NULLIF(ABS(value), 0)) returns NULL for zero rather than a potential error.

Try It Yourself

Write a comprehensive product profitability report using math functions. For each product category show: category, product_count, total_units_sold (0 if none sold), total_revenue (0 if none), total_cogs (sum of cost_price × quantity, 0 if none), gross_profit (revenue minus cogs), gross_margin_pct (gross_profit / revenue × 100, rounded to 1dp, NULL if no revenue), avg_unit_price (rounded to 2dp), price_stddev (rounded to 2dp), price_cv_pct (coefficient of variation = stddev/avg × 100, rounded to 1dp, NULL if avg is 0), and a profitability_band: 'High Margin' if gross_margin_pct >= 40, 'Good Margin' if >= 25, 'Low Margin' if >= 10, 'Loss/Unknown' otherwise. Sort by gross_margin_pct descending, NULLs last.

🎯 Key Takeaways

✓ROUND(n, dp) rounds to dp decimal places. Always specify decimal places explicitly for currency — ROUND(amount, 2). FLOOR always rounds down; CEIL always rounds up; TRUNC cuts without rounding.
✓ABS returns the magnitude regardless of sign. Use it when you care about how far apart two values are, not which is larger — deviations, tolerances, outlier distances.
✓MOD(a, b) returns the remainder after division. Use it for cycling (even/odd, A/B buckets), last-N-digit extraction, and identifying multiples.
✓NULLIF(denominator, 0) is mandatory for all division operations. It converts zero to NULL — NULL / anything = NULL (no error). Pair with COALESCE if you need 0 instead of NULL for display.
✓Integer division trap: 7 / 2 = 3 in SQL when both are integers. Cast the numerator to NUMERIC before dividing: 7::NUMERIC / 2 = 3.5. This applies to all percentage and ratio calculations.
✓POWER(base, exponent) for exponentiation. SQRT for square roots. Use POWER(x, 0.5) only on non-negative values — negative inputs return NULL or error.
✓LOG(n) is base-10 logarithm. LN(n) is natural logarithm. FLOOR(LOG10(value)) gives the order of magnitude — useful for log-scale bucketing.
✓STDDEV returns NULL for single-row groups (n-1 = 0). Use STDDEV_POP or guard with CASE WHEN COUNT(*) > 1 THEN STDDEV(col) END.
✓PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col) computes the median. Use PERCENTILE_CONT for the five-number summary: min, Q1, median, Q3, max.
✓WIDTH_BUCKET(value, min, max, n) assigns values to n uniform-width buckets. Use CASE ranges for business-meaningful non-uniform buckets.

What comes next

In Module 44, you learn CAST and type conversion — converting between data types, implicit vs explicit casting, safe casting with TRY_CAST, and the type coercion rules that prevent silent errors.

Module 44 → CAST and Type Conversion

Discussion

Have a better approach? Found something outdated? Share it — your knowledge helps everyone learning here.

Continue with GitHub