SQL ROUND, FLOOR, CEIL & TRUNC: Numeric Rounding for Reporting & Finance

sql round looks like a one-line helper — round a price to two decimals, ship the report. Senior data engineers know the truth: rounding is the single largest source of "off-by-a-penny" tickets in finance pipelines, and every dialect has a slightly different idea of what ROUND(2.5) should return. The gap between the school-grade "round half up" rule and IEEE-754's banker's rounding is small enough to be invisible on one row and large enough to cost an auditor's signature across a million.

This cheat sheet is the field guide for every engineer wiring numeric rounding into a revenue model, a tax calculation, or a regulatory export. It walks through the four-function matrix (ROUND, FLOOR, CEIL, TRUNC) and the negative-number gotchas that separate them, the NUMERIC(p, s) precision contract, the half-up vs banker's-rounding (HALF_EVEN) split that powers financial reporting, the dialect cheat sheet across Postgres, MySQL, SQL Server, Snowflake, BigQuery and Oracle, and the canonical recipes for sql round to 2 decimals and rounding to the nearest five cents. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the aggregation practice library →, rehearse on arithmetic expression problems →, and stack the precision muscles with type-casting drills →.

---

On this page

• Why rounding is the silent bug magnet in finance
• ROUND, FLOOR, CEIL, TRUNC — function matrix
• NUMERIC(p, s) precision and scale — the rounding contract
• Banker's rounding vs half-up — the regulatory split
• Dialect cheat sheet — Postgres, MySQL, SQL Server, Snowflake, BigQuery, Oracle
• Cheat sheet — rounding recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why rounding is the silent bug magnet in finance

Rounding errors are invisible on one row and lethal on a million — every fraction of a cent compounds into a reconciliation ticket

The one-sentence invariant: rounding never changes a single row by more than half a unit, but applied to N rows it can drift the total by up to N × 0.5 units — and over a million-row revenue table that drift is reported as a $5,000 variance that the auditor refuses to sign off. Once you internalise that small per-row errors compound, every rounding decision becomes a deliberate choice instead of an afterthought.

The penny-drift problem in three rows.

• One row at 0.005 rounded the wrong way costs 0.005. Invisible.
• A thousand rows × 0.005 = $5.00. Caught in a quarterly close.
• A million rows × 0.005 = $5,000. Caught on day one by a finance manager who reconciles the report against a downstream system using a different rounding rule.

The pattern is the same whether the rows are tax line items, foreign-exchange conversions, fractional shares, or pro-rated subscription fees. The cure is not "round more carefully" — it is "be explicit about the rounding rule, store the raw value at the highest precision, and round only at the presentation layer."

The three places rounding bites.

• Aggregates. SUM(ROUND(amount, 2)) is not the same as ROUND(SUM(amount), 2). The first rounds every row before summing — drift accumulates. The second sums at full precision then rounds once — drift bounded by 0.005.
• Currency display. A web app shows $1,234.57. A back-end report shows $1,234.566.... If both pull from the same column but render with different rounding, the customer-service ticket reads "your dashboard contradicts your invoice."
• Regulatory reports. Tax authorities care about a specific rounding rule. The IRS expects half-up. IFRS expects half-even. Get the wrong one and the entire report is rejected.

Half-up vs half-even vs truncate.

The three competing rules for the "what do I do with exactly 0.5?" question are the entire argument behind every rounding interview probe.

• Half-up rounds 0.5 always away from zero: 0.5 → 1, 1.5 → 2, 2.5 → 3. The school-grade rule. Bias: every .5 adds half a unit on average, so a million rows accumulate +0.5 × density of drift.
• Half-even (banker's) rounds 0.5 to the nearest even integer: 0.5 → 0, 1.5 → 2, 2.5 → 2. The IEEE-754 default. Bias: zero on average across uniformly distributed inputs because half the time .5 rounds up and half the time it rounds down.
• Truncate drops everything past the precision boundary without rounding: 2.5 → 2, 2.9 → 2, -2.5 → -2. The "I will lose data on purpose" mode — used in compliance scenarios where over-stating any single line item is a violation.

NUMERIC vs FLOAT — why DOUBLE PRECISION should never hold money.

The hard rule: never store currency in FLOAT, REAL, or DOUBLE PRECISION. Binary floats cannot exactly represent decimal fractions like 0.1 or 0.20. Add 0.1 + 0.2 and you get 0.30000000000000004. Multiply that by a million row count and your "total revenue" reads $1,000,000.40 instead of $1,000,000.00. The fix is NUMERIC(p, s) (also spelt DECIMAL(p, s)) — an arbitrary-precision base-10 type. Trade speed for correctness; finance always picks correctness.

The "round at the edge" rule.

• Store raw. Every source-of-truth column holds the highest precision the source emits — typically NUMERIC(18, 6) for prices, NUMERIC(20, 4) for currency totals.
• Compute raw. Aggregations, joins, FX conversions all happen at full precision. Drift propagates as zero rounding because no rounding happens.
• Round at the SELECT. The presentation layer — the dashboard query, the CSV export, the API endpoint — runs ROUND(x, 2) on the way out. One rounding step, predictable bias, one place to audit.

Worked example — SUM(ROUND) vs ROUND(SUM) on a million rows

Detailed explanation. Reporting teams routinely write SUM(ROUND(amount, 2)) because they want every line item to display cleanly. The trap: each row rounds independently, each rounding adds a small bias, and the total drifts. The correct form is ROUND(SUM(amount), 2) — sum at raw precision, round once.

Question. Given a line_items table with 10 sample rows whose amount ends in .005, compute the total revenue two ways and explain why the numbers diverge. Which one matches the auditor's spreadsheet?

Input.

row	amount
1	1.005
2	1.005
3	1.005
4	1.005
5	1.005
6	1.005
7	1.005
8	1.005
9	1.005
10	1.005

Code.

SELECT
    SUM(ROUND(amount, 2)) AS sum_of_rounded,
    ROUND(SUM(amount), 2) AS rounded_sum,
    SUM(amount)           AS raw_sum
FROM line_items;

Step-by-step explanation.

1. ROUND(1.005, 2) rounds to 1.01 under half-up (most dialects on NUMERIC). Ten rows of 1.01 summed give 10.10.
2. SUM(amount) first totals the raw values: 10 × 1.005 = 10.05. ROUND(10.05, 2) then rounds to 10.05 (no rounding needed past 2 decimals).
3. The two answers differ by 0.05 on ten rows. Scale to one million identical rows and the difference is $5,000.
4. The auditor's spreadsheet sums raw line items at full precision and rounds once at the bottom — so ROUND(SUM(amount), 2) is the one that matches. SUM(ROUND(amount, 2)) is the one that drifts.

Output.

sum_of_rounded	rounded_sum	raw_sum
10.10	10.05	10.05

Rule of thumb. Round once, at the outermost SELECT. Every inner aggregate should consume raw NUMERIC(p, s) values. The only exception is when each row is itself a presentation row (e.g. an invoice line item the customer sees in pennies) — and even then, the total should sum the raw column, not the rounded one.

Worked example — FLOAT silently breaks currency arithmetic

Detailed explanation. A startup stores price as DOUBLE PRECISION because "it is fast and good enough." Three months later a customer reports a $0.01 discrepancy between the invoice total and the line-item sum. The investigation reveals that 0.1 + 0.2 in IEEE-754 binary floats is not 0.3. Switching the column to NUMERIC(18, 4) fixes the bug permanently.

Question. Show the discrepancy between FLOAT arithmetic and NUMERIC arithmetic on a simple 0.1 + 0.2 example, and explain why finance teams always pick NUMERIC.

Input.

op	a	b
add	0.1	0.2
add	1.05	1.05
mul	0.07	100.0

Code.

SELECT
    (0.1::float8 + 0.2::float8)         AS float_add,
    (0.1::numeric + 0.2::numeric)       AS numeric_add,
    (1.05::float8 + 1.05::float8)       AS float_add_2,
    (1.05::numeric + 1.05::numeric)     AS numeric_add_2,
    (0.07::float8 * 100.0::float8)      AS float_mul,
    (0.07::numeric * 100.0::numeric)    AS numeric_mul;

Step-by-step explanation.

1. 0.1::float8 + 0.2::float8 evaluates to 0.30000000000000004 — IEEE-754 cannot represent 0.1 exactly in binary, so the sum carries a tiny "round-off" digit at the 17th place.
2. 0.1::numeric + 0.2::numeric evaluates to exactly 0.3 — NUMERIC stores values in base 10 with arbitrary precision, so there is no binary round-off.
3. 1.05::float8 + 1.05::float8 evaluates to 2.1000000000000001. Again, the inexact binary representation of 1.05 leaks a trailing digit into the sum.
4. 0.07 * 100.0 in FLOAT produces 7.000000000000001; in NUMERIC the answer is 7.00. Multiply that by ten million rows and you have a real seven-cent variance.
5. The cost: FLOAT is faster (single CPU instruction) but cannot model decimal currency exactly. NUMERIC is slower (software multi-digit math) but exact. Finance teams pick exactness every time.

Output.

float_add	numeric_add	float_add_2	numeric_add_2	float_mul	numeric_mul
0.30000000000000004	0.3	2.1000000000000001	2.10	7.000000000000001	7.00

Rule of thumb. If a column ever holds money, tax, FX, or anything that is summed and signed off by an auditor, store it as NUMERIC(p, s). Use FLOAT for measurements (sensor readings, scores, distances) where the last digit is noise anyway. The performance gain from FLOAT is never worth a single quarter-end reconciliation meeting.

Worked example — the "round at the edge" rule applied to a revenue dashboard

Detailed explanation. A SaaS company has a revenue dashboard with three tiles: total revenue, average deal size, and median deal size. Every tile reads from the same deals table where amount is NUMERIC(18, 6). The correct architecture is to store raw, compute raw, and round at each tile's outermost SELECT — not at write time, not in a middle CTE.

Question. Given a deals table at NUMERIC(18, 6) precision, write the dashboard query so that each tile gets a single rounding step at the SELECT and the underlying CTEs hold raw precision.

Input — deals.

deal_id	amount
1	1500.123456
2	2200.654321
3	800.555555
4	4500.000000

Code.

WITH raw_aggs AS (
    SELECT
        SUM(amount)                                      AS total_amount_raw,
        AVG(amount)                                      AS avg_amount_raw,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount_raw
    FROM deals
)
SELECT
    ROUND(total_amount_raw,  2) AS total_revenue,
    ROUND(avg_amount_raw,    2) AS avg_deal_size,
    ROUND(median_amount_raw, 2) AS median_deal_size
FROM raw_aggs;

Step-by-step explanation.

1. The raw_aggs CTE computes every aggregate at the raw NUMERIC(18, 6) precision. SUM is exact. AVG is SUM / COUNT carried out in NUMERIC arithmetic — no FLOAT round-off, no early rounding.
2. The outer SELECT runs ROUND(x, 2) exactly once per tile. The drift bound on every output is half a cent, regardless of how many input rows fed the aggregate.
3. If a downstream consumer needs four decimals (e.g. an FX desk), the outer SELECT changes ROUND(x, 2) to ROUND(x, 4). No CTE rewrite required.
4. If the dashboard later adds a "revenue by region" tile, the SUM happens at raw precision inside the CTE, GROUP BY adds the region key, and the outer SELECT still owns the single rounding step.

Output.

total_revenue	avg_deal_size	median_deal_size
9001.33	2250.33	1850.39

Rule of thumb. Architect every reporting query as "raw CTE → presentation SELECT." Rounding lives only in the presentation SELECT, and there it is one explicit ROUND(x, n) per output column. Auditors love the pattern because there is one place to inspect for every off-by-a-penny bug.

SQL interview question on the rounding contract

A senior interviewer often opens with: "Walk me through every place rounding could change a revenue total — write time, JOIN time, aggregate time, presentation time — and how you'd architect a finance pipeline so that there is exactly one rounding step end-to-end." It blends FLOAT-vs-NUMERIC, the SUM/ROUND ordering trap, and the "round at the edge" rule into a single design probe.

Solution Using the raw-CTE + presentation-SELECT pattern

-- 1) Source-of-truth raw table — wide precision, never rounded
CREATE TABLE deals (
    deal_id     bigint PRIMARY KEY,
    region      text NOT NULL,
    amount_raw  numeric(18, 6) NOT NULL,
    booked_at   timestamptz NOT NULL
);

-- 2) Presentation query — single rounding step at the outer SELECT
WITH region_aggs AS (
    SELECT
        region,
        SUM(amount_raw)                          AS total_raw,
        AVG(amount_raw)                          AS avg_raw,
        COUNT(*)                                 AS deal_count
    FROM deals
    WHERE booked_at >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY region
)
SELECT
    region,
    ROUND(total_raw, 2) AS total_revenue,
    ROUND(avg_raw,   2) AS avg_deal_size,
    deal_count
FROM region_aggs
ORDER BY total_revenue DESC;

Step-by-step trace.

region	rows	SUM(amount_raw)	AVG(amount_raw)	ROUND(total, 2)	ROUND(avg, 2)
EU	3	4501.333332	1500.444444	4501.33	1500.44
US	2	5300.654321	2650.327161	5300.65	2650.33
APAC	1	800.555555	800.555555	800.56	800.56

The trace shows that every aggregate is computed at the raw NUMERIC(18, 6) precision in the CTE. Only the outermost SELECT applies ROUND(x, 2) — once per output column. Drift on each output is bounded by half a cent.

Output:

region	total_revenue	avg_deal_size	deal_count
US	5300.65	2650.33	2
EU	4501.33	1500.44	3
APAC	800.56	800.56	1

Why this works — concept by concept:

• Raw at NUMERIC(18, 6) — six decimals of source-of-truth precision is enough to model penny-fractions for any normal currency. The column never holds FLOAT, so binary round-off never enters the pipeline.
• Compute raw in the CTE — every SUM, AVG, COUNT runs at full precision. The optimiser hashes by region, sums into a NUMERIC accumulator, and emits raw values to the next stage.
• Round once at the SELECT — the outer SELECT applies ROUND(x, 2) exactly once per tile. The drift is bounded by 0.005 per output row regardless of input count.
• One place to audit — every off-by-a-penny investigation starts at the outer SELECT. The auditor only has to inspect one ROUND(...) call per tile to verify the rounding rule.
• Cost — same as a plain GROUP BY: O(rows) for the aggregate; ROUND is a constant-time scalar op. Zero performance penalty for the architecture.

SQL
Topic — aggregation
Aggregation problems (SQL)

Practice →

SQL
Topic — financial-data
Financial data problems (SQL)

Practice →

---

2. ROUND, FLOOR, CEIL, TRUNC — function matrix

Four functions, same input, four different answers — and the negative-number rules are where junior engineers get bitten

The mental model in one line: ROUND is "nearest", FLOOR is "down toward minus infinity", CEIL is "up toward plus infinity", and TRUNC is "drop digits toward zero" — and the difference between FLOOR and TRUNC only shows up on negative numbers. Once you say it out loud, every interview probe in this section becomes a memorisation exercise.

The four-function matrix on four signed inputs.

Function	2.5	-2.5	2.4	-2.4
ROUND(x)	3	-3	2	-2
FLOOR(x)	2	-3	2	-3
CEIL(x) / CEILING(x)	3	-2	3	-2
TRUNC(x)	2	-2	2	-2

The single most important row to memorise is FLOOR(-2.4) = -3 vs TRUNC(-2.4) = -2. FLOOR always rounds toward minus infinity — for negative numbers that means "more negative." TRUNC always rounds toward zero — for negative numbers that means "less negative." The same -2.4 input gives two different integers depending on which function you reach for, and a junior engineer who treats them as interchangeable will silently overcount or undercount the negative tail of every distribution.

ROUND in detail.

• Signature. ROUND(x) or ROUND(x, n). n defaults to 0. n can be negative — ROUND(1234, -2) returns 1200.
• Rule. Round to the nearest integer (or nearest 10^-n). The half-case (.5 exactly) is dialect-dependent — half-up on most NUMERIC dialects, half-even on Postgres DOUBLE PRECISION, configurable on BigQuery.
• Negative-number behaviour. Symmetric around zero under half-up: ROUND(2.5) = 3, ROUND(-2.5) = -3. Under half-even: ROUND(2.5) = 2, ROUND(-2.5) = -2.
• Use case. Default rounding for presentation. sql round to 2 decimals for currency. Negative n for thousands/millions reporting.

FLOOR in detail.

• Signature. FLOOR(x). No precision argument in most dialects (Snowflake / BigQuery accept a second arg via FLOOR(x, n) extension).
• Rule. Largest integer ≤ x. Always rounds toward minus infinity.
• Negative-number behaviour. FLOOR(-2.4) = -3. The minus-infinity rule means negative non-integers always go more negative.
• Use case. "Round down to the cent" via FLOOR(x * 100) / 100. "Compute days elapsed" via FLOOR(EXTRACT(EPOCH FROM (b - a)) / 86400). "Index into a bucket" — FLOOR(price / 10) * 10.

CEIL / CEILING in detail.

• Signature. CEIL(x) (most dialects) or CEILING(x) (SQL Server only takes CEILING, Oracle only takes CEIL). Snowflake / Postgres / BigQuery accept both.
• Rule. Smallest integer ≥ x. Always rounds toward plus infinity.
• Negative-number behaviour. CEIL(-2.4) = -2. The plus-infinity rule means negative non-integers always go less negative.
• Use case. "Round up to the dollar." "Compute the number of pages needed" via CEIL(rows / page_size). "Bid up to the next penny" in pricing engines.

TRUNC in detail.

• Signature. TRUNC(x) or TRUNC(x, n) (most dialects). MySQL spells it TRUNCATE(x, n). SQL Server has no TRUNC and instead uses ROUND(x, n, 1) where the third argument 1 means "truncate, do not round."
• Rule. Drop digits past the precision boundary. Always rounds toward zero — never changes the integer part for |x| < 1 once digits are dropped.
• Negative-number behaviour. TRUNC(-2.4) = -2 and TRUNC(-2.9) = -2 — both go less negative (toward zero). This is the single line that makes TRUNC ≠ FLOOR on negatives.
• Use case. Compliance reporting where overstating any line item is a violation. Statistics work where you want to drop noisy fractions without bias. Bit-packing rows into a fixed-size payload.

The single most common rounding bug in finance code.

A team writes FLOOR(amount * 100) / 100 to "round down to the nearest cent" because they want to under-bill rather than over-bill. The query works perfectly until a refund row arrives with a negative amount. FLOOR(-1.234 * 100) / 100 = FLOOR(-123.4) / 100 = -124 / 100 = -1.24 — which is more negative than the input, increasing the magnitude of the refund. The fix is TRUNC(amount * 100) / 100 — which goes toward zero on both signs and "rounds down in magnitude" regardless of sign.

Worked example — the four functions on 2.5, -2.5, 2.4, -2.4

Detailed explanation. The canonical interview probe: take four signed inputs and produce sixteen results across the four functions. Hand-tracing the matrix is the fastest way to internalise the negative-number rules and the difference between FLOOR (toward -∞) and TRUNC (toward 0).

Question. Given the input set {2.5, -2.5, 2.4, -2.4}, compute ROUND, FLOOR, CEIL, and TRUNC for each value. Show the full matrix and explain why FLOOR and TRUNC disagree on every negative input.

Input.

x
2.5
-2.5
2.4
-2.4

Code.

SELECT
    x,
    ROUND(x)  AS round_x,
    FLOOR(x)  AS floor_x,
    CEIL(x)   AS ceil_x,
    TRUNC(x)  AS trunc_x
FROM (VALUES (2.5), (-2.5), (2.4), (-2.4)) AS t(x);

Step-by-step explanation.

1. ROUND(2.5) under half-up evaluates to 3 (the nearest integer; tie goes away from zero). ROUND(-2.5) under half-up evaluates to -3 by symmetry.
2. FLOOR(2.5) is the largest integer ≤ 2.5 → 2. FLOOR(-2.5) is the largest integer ≤ -2.5 → -3 (because -3 < -2.5 < -2, and we want the largest integer that does not exceed -2.5).
3. CEIL(2.5) is the smallest integer ≥ 2.5 → 3. CEIL(-2.5) is the smallest integer ≥ -2.5 → -2.
4. TRUNC(2.5) drops the .5 → 2. TRUNC(-2.5) drops the .5 → -2 (truncate toward zero — the sign is preserved, the magnitude is reduced).
5. For 2.4: ROUND → 2, FLOOR → 2, CEIL → 3, TRUNC → 2. For -2.4: ROUND → -2, FLOOR → -3, CEIL → -2, TRUNC → -2.
6. The mismatch row: FLOOR(-2.4) = -3 vs TRUNC(-2.4) = -2. Same input, two different answers. Memorise this.

Output.

x	ROUND	FLOOR	CEIL	TRUNC
2.5	3	2	3	2
-2.5	-3	-3	-2	-2
2.4	2	2	3	2
-2.4	-2	-3	-2	-2

Rule of thumb. When in doubt on negative inputs, draw a number line and circle "toward zero" (TRUNC), "toward minus infinity" (FLOOR), and "toward plus infinity" (CEIL). The three rules are mutually exclusive on negative non-integers — any code that confuses them will produce a sign-dependent bug.

Worked example — "round down to the nearest cent" the right way for both signs

Detailed explanation. A billing pipeline wants to round every line item down to the cent so the customer is never overcharged by a fraction. The classic implementation FLOOR(amount * 100) / 100 works for positive amounts and silently betrays the team on negative refund rows. The correct implementation uses TRUNC or branches on sign.

Question. Given a transactions table with mixed positive (charges) and negative (refunds) amounts, write the expression that "rounds down in magnitude" — i.e. always reduces the absolute value, never increases it. Show why FLOOR is wrong on negatives.

Input.

tx_id	amount
1	12.345
2	-7.891
3	5.000
4	-3.999

Code.

SELECT
    tx_id,
    amount,
    -- BUGGY for negative refunds — FLOOR(-7.891 * 100)/100 = -7.90
    FLOOR(amount * 100) / 100                 AS floor_to_cent,
    -- CORRECT — TRUNC rounds toward zero on both signs
    TRUNC(amount * 100) / 100                 AS trunc_to_cent,
    -- Equivalent branched form
    SIGN(amount) * FLOOR(ABS(amount) * 100) / 100 AS branched_to_cent
FROM transactions;

Step-by-step explanation.

1. For tx_id=1 (12.345): both FLOOR(1234.5)/100 = 12.34 and TRUNC(1234.5)/100 = 12.34. Both correctly under-charge by half a cent.
2. For tx_id=2 (-7.891): FLOOR(-789.1)/100 = -790/100 = -7.90. The magnitude is now 7.90 — larger than the original 7.891 — so the refund grew by a cent. Bug.
3. For tx_id=2 under TRUNC: TRUNC(-789.1)/100 = -789/100 = -7.89. Magnitude 7.89 < 7.891. Correctly reduced.
4. For tx_id=3 (5.000): both FLOOR and TRUNC give 5.00 — no rounding needed.
5. For tx_id=4 (-3.999): FLOOR → -4.00 (magnitude grew). TRUNC → -3.99 (magnitude shrunk).
6. The branched form SIGN(x) * FLOOR(ABS(x) * 100) / 100 is mathematically equivalent to TRUNC but verbose. Use it when working in a dialect that lacks a TRUNC operator on the second argument.

Output.

tx_id	amount	floor_to_cent (BUG)	trunc_to_cent (OK)	branched_to_cent
1	12.345	12.34	12.34	12.34
2	-7.891	-7.90	-7.89	-7.89
3	5.000	5.00	5.00	5.00
4	-3.999	-4.00	-3.99	-3.99

Rule of thumb. Anywhere a column can be negative — refunds, FX deltas, GL adjustments, return ratings — never reach for FLOOR if your business rule is "reduce magnitude." Use TRUNC, which rounds toward zero on both signs and matches every junior engineer's mental model of "drop the decimals."

Worked example — CEIL for page-count and quota arithmetic

Detailed explanation. A typical use case for CEIL is "how many pages do I need to show all the rows?" The naive rows / page_size returns a fractional answer; you need to round up to the next whole page because half a page is still a full page in display terms. CEIL captures the rule in one operator.

Question. Given a table of category row counts and a page size, compute the number of pages required for each category. Show the integer-arithmetic gotcha and the explicit CEIL fix.

Input.

category	rows
A	100
B	105
C	99
D	1

Code.

SELECT
    category,
    rows,
    -- BUGGY — integer division truncates the fraction away
    rows / 10                       AS pages_int_div,
    -- CORRECT — explicit CEIL on float division
    CEIL(rows * 1.0 / 10)           AS pages_ceil,
    -- Equivalent integer-only form
    (rows + 9) / 10                 AS pages_int_trick
FROM categories;

Step-by-step explanation.

1. For category=A (100 rows): 100 / 10 = 10 exactly. CEIL also = 10. Both correct.
2. For category=B (105 rows): 105 / 10 in integer arithmetic is 10 (the .5 is truncated). The dashboard would show 10 pages but the 105th row would not appear. Bug.
3. For category=B under CEIL: CEIL(105 * 1.0 / 10) = CEIL(10.5) = 11. The 105th row appears on page 11. Correct.
4. For category=C (99 rows): integer division → 9 (the 99th row is lost). CEIL → 10. CEIL correct.
5. The "+9 / 10" trick is the integer-only version of CEIL: add (divisor - 1) before dividing. Works for non-negative numerator and positive denominator. Use it when you cannot escape integer arithmetic (rare in modern SQL).

Output.

category	rows	pages_int_div (BUG)	pages_ceil (OK)	pages_int_trick
A	100	10	10	10
B	105	10	11	11
C	99	9	10	10
D	1	0	1	1

Rule of thumb. Whenever the business rule is "round up to cover the whole input," reach for CEIL. Common cases: pagination, capacity planning ("how many trucks for N pallets?"), batch sizing ("how many ETL chunks for N rows?"), tier counts ("how many licenses for N users?").

Worked example — TRUNC for compliance reporting

Detailed explanation. Tax authorities sometimes require that tax owed be truncated to the cent, not rounded — so the taxpayer never overpays the fractional cent. The rule is "drop everything past the decimal precision, do not round up under any circumstance." TRUNC is the operator.

Question. Given an invoices table with raw tax amounts at NUMERIC(18, 6) precision, compute the tax-owed column under three rules: ROUND, FLOOR, and TRUNC. Show why the regulator picks TRUNC.

Input.

invoice_id	tax_raw
1	14.236789
2	22.998001
3	0.005000
4	99.499999

Code.

SELECT
    invoice_id,
    tax_raw,
    ROUND(tax_raw, 2) AS tax_round_2dp,
    FLOOR(tax_raw * 100) / 100 AS tax_floor_2dp,
    TRUNC(tax_raw, 2) AS tax_trunc_2dp
FROM invoices;

Step-by-step explanation.

1. For invoice_id=1 (14.236789): ROUND → 14.24, FLOOR → 14.23, TRUNC → 14.23. ROUND would overpay by 0.003 cents; TRUNC matches the regulator's "never round up" rule.
2. For invoice_id=2 (22.998001): ROUND → 23.00, FLOOR → 22.99, TRUNC → 22.99. ROUND would overpay by 0.001999; TRUNC is correct.
3. For invoice_id=3 (0.005): ROUND → 0.01 (half-up dialects) or 0.00 (half-even). FLOOR → 0.00. TRUNC → 0.00. TRUNC is unambiguous.
4. For invoice_id=4 (99.499999): ROUND → 99.50, FLOOR → 99.49, TRUNC → 99.49. TRUNC reduces overpayment to the regulator's preferred precision.
5. Note that all tax_raw values are positive, so FLOOR and TRUNC coincide here. On negative values (refund credits) FLOOR would understate the refund (more negative), TRUNC would correctly stay closer to zero.

Output.

invoice_id	tax_raw	ROUND	FLOOR	TRUNC
1	14.236789	14.24	14.23	14.23
2	22.998001	23.00	22.99	22.99
3	0.005000	0.01	0.00	0.00
4	99.499999	99.50	99.49	99.49

Rule of thumb. When the regulator says "truncate, do not round," they almost never mean FLOOR — they mean TRUNC. The distinction only matters when negatives are possible, but the law tends to be written assuming positives. Use TRUNC for portability and so the rule still holds the day a credit memo introduces a negative row.

SQL interview question on the function matrix

A senior interviewer often asks: "Without running anything, write down the integer answer for ROUND(-2.5), FLOOR(-2.5), CEIL(-2.5), and TRUNC(-2.5) on Postgres NUMERIC. Then explain which two functions disagree and why." It is the fastest litmus test for whether the candidate knows the negative-number rules.

Solution Using the four-function trace by hand

-- The trace, expressed as SQL so the candidate can verify against the engine
SELECT
    ROUND(-2.5::numeric) AS round_neg_25,   -- -3 (half-up)
    FLOOR(-2.5::numeric) AS floor_neg_25,   -- -3 (toward -infinity)
    CEIL (-2.5::numeric) AS ceil_neg_25,    -- -2 (toward +infinity)
    TRUNC(-2.5::numeric) AS trunc_neg_25;   -- -2 (toward zero)

Step-by-step trace.

Function	-2.5	rule	result
ROUND (half-up NUMERIC)	-2.5	tie → away from zero	-3
FLOOR	-2.5	toward -∞	-3
CEIL	-2.5	toward +∞	-2
TRUNC	-2.5	toward 0	-2

The interviewer is listening for two things: (1) the candidate writes -3 for both ROUND and FLOOR — that is the half-up coincidence on -2.5; (2) the candidate writes -2 for both CEIL and TRUNC — that is the toward-zero / toward-+∞ coincidence on -2.5. Either mistake reveals the candidate has memorised the positive-number table but not internalised the negative-number directions.

Output:

round_neg_25	floor_neg_25	ceil_neg_25	trunc_neg_25
-3	-3	-2	-2

Why this works — concept by concept:

• ROUND is "nearest" — under half-up, -2.5 is equidistant from -3 and -2, and the tie-breaker "away from zero" picks -3. Under half-even, the tie-breaker "nearest even" picks -2. Same input, two valid answers depending on dialect.
• FLOOR is monotonic toward minus infinity — FLOOR(x) is the unique largest integer ≤ x. On negatives that means the integer further from zero.
• CEIL is monotonic toward plus infinity — CEIL(x) is the unique smallest integer ≥ x. On negatives that means the integer closer to zero.
• TRUNC is symmetric around zero — TRUNC(x) = SIGN(x) × FLOOR(ABS(x)). The function never crosses zero; it only shrinks magnitude.
• The two coincidences — on -2.5, ROUND and FLOOR agree (under half-up) because the tie-break direction happens to be the same as the minus-infinity direction. CEIL and TRUNC agree because both choose -2. On -2.4 or -2.6 the agreements break.
• Cost — every function is O(1) on a single value. Pure compute, no I/O. The performance difference between ROUND and FLOOR is unmeasurable; pick on semantics, not on speed.

SQL
Topic — arithmetic-expressions
Arithmetic expression problems (SQL)

Practice →

SQL
Topic — math
Math problems (SQL)

Practice →

---

3. NUMERIC(p, s) precision and scale — the rounding contract

decimal precision is a contract, not a hint — NUMERIC(p, s) declares "p total digits, s after the decimal" and the engine enforces it

The mental model in one line: NUMERIC(p, s) reads as "p total significant digits, s of which sit after the decimal point" — so NUMERIC(10, 2) holds up to 99,999,999.99 and silently rounds any value with more than two decimals on INSERT. Get the contract right at the column level and 90% of the rounding bugs never enter the database.

The precision contract in five bullets.

• NUMERIC(p, s) — p is total digits (precision), s is digits after the decimal point (scale). p ≥ s always.
• NUMERIC(10, 2) — max value 99,999,999.99. Try to store 100,000,000.00 and Postgres raises numeric field overflow.
• NUMERIC (no args) — arbitrary precision. Postgres supports up to 131,072 digits before the decimal and 16,383 after. Slower but unbounded.
• DECIMAL(p, s) — exact synonym for NUMERIC(p, s) in every major dialect except SQL Server, where DECIMAL and NUMERIC are equivalent but MONEY is a separate 8-byte fixed-precision type.
• Implicit rounding on INSERT. Inserting a value with more decimal digits than the scale silently rounds (most dialects) or raises (strict mode in MySQL 8 with STRICT_TRANS_TABLES).

DECIMAL vs NUMERIC vs MONEY — three names, slightly different behaviours.

• NUMERIC / DECIMAL — ANSI standard, exact base-10 fixed-precision, arbitrary p and s. Use everywhere money matters.
• MONEY (SQL Server, Postgres) — fixed 4-decimal (Postgres) or 4-decimal (SQL Server) fixed-point type, faster than NUMERIC for arithmetic but locks the scale at 4. Do not use in new code — the type predates ANSI NUMERIC and is now a portability landmine.
• FLOAT / REAL / DOUBLE PRECISION — IEEE-754 binary floats. Fast, inexact, never for money. Use for measurements, scores, distances, ratios.

Scale arithmetic — what happens when you multiply two NUMERICs.

The Postgres rule, repeated in most dialects: the product of NUMERIC(p1, s1) and NUMERIC(p2, s2) is NUMERIC(p1 + p2, s1 + s2) — both precision and scale add. Multiply NUMERIC(10, 2) by NUMERIC(10, 4) and you get NUMERIC(20, 6). The scale grows with every multiplication; if you never round, your column type rapidly hits the system maximum.

Casting traps.

• ::numeric(10, 2) rounds half-up to two decimals. 1.555::numeric(10, 2) → 1.56 on Postgres NUMERIC.
• ::int truncates toward zero. 1.999::int → 1. (-1.999)::int → -1. Same as TRUNC.
• CAST(x AS DECIMAL(10, 2)) is the ANSI form of the cast — same rounding rules as ::numeric(10, 2).

Worked example: storage vs presentation.

Store at NUMERIC(18, 6), present at NUMERIC(10, 2). The raw column holds whatever the source emits — six decimals of source-of-truth precision. The presentation layer casts or rounds to two decimals only at SELECT time. Auditors love this because there is one column to inspect for "what did we capture from the source" and one query to inspect for "what did we show the customer."

Worked example — implicit rounding on INSERT

Detailed explanation. A new analyst inserts 1.555 into a NUMERIC(10, 2) column expecting an error or a warning. The engine silently rounds to 1.56 (Postgres half-up) or 1.55 (Postgres DOUBLE / banker's). No warning, no log line. The bug is "the database silently changed my data."

Question. Given a target column price NUMERIC(10, 2), insert four values with varying decimal precision and show what the engine actually stores. Discuss the half-up vs half-even split.

Input rows to INSERT.

row	value
1	1.555
2	1.554
3	1.556
4	0.005

Code.

CREATE TEMP TABLE rounding_demo (price numeric(10, 2));
INSERT INTO rounding_demo VALUES (1.555), (1.554), (1.556), (0.005);
SELECT * FROM rounding_demo;

Step-by-step explanation.

1. 1.555 is exactly half between 1.55 and 1.56. Postgres NUMERIC uses half-up → stored as 1.56.
2. 1.554 is closer to 1.55 than to 1.56. Stored as 1.55.
3. 1.556 is closer to 1.56 than to 1.55. Stored as 1.56.
4. 0.005 is the classic test case: half between 0.00 and 0.01. Half-up → 0.01. Half-even (banker's) → 0.00 (because 0 is the even integer at that scale).
5. The bug surface: the INSERT raised no error and no warning. A staging-table audit query would show 1.555 was stored as 1.56 and the analyst's downstream JOIN on raw value would silently fail to find the row.

Output (Postgres NUMERIC, half-up).

price
1.56
1.55
1.56
0.01

Rule of thumb. Never let the column type be the rounding mechanism. Declare the staging table at the highest precision the source emits (NUMERIC(18, 6)), and let an explicit ROUND in a downstream view convert to the presentation NUMERIC(10, 2). Implicit rounding on INSERT is a silent landmine.

Worked example — scale arithmetic on a product

Detailed explanation. Multiply quantity (NUMERIC(10, 2)) by unit_price (NUMERIC(10, 4)) and the result type is NUMERIC(20, 6). The presentation layer almost never wants six decimals, so an explicit ROUND to the target scale is mandatory before SELECT.

Question. Given a line_items table with quantity NUMERIC(10, 2) and unit_price NUMERIC(10, 4), compute line_total and show the scale that the engine infers. Then add a ROUND(line_total, 2) to bring it back to currency precision.

Input.

item_id	quantity	unit_price
1	2.50	12.3400
2	1.25	99.9999
3	100.00	0.0123

Code.

SELECT
    item_id,
    quantity,
    unit_price,
    quantity * unit_price                          AS line_total_raw,
    pg_typeof(quantity * unit_price)               AS inferred_type,
    ROUND(quantity * unit_price, 2)                AS line_total_2dp
FROM line_items;

Step-by-step explanation.

1. quantity is NUMERIC(10, 2). unit_price is NUMERIC(10, 4). The product type is NUMERIC(10+10, 2+4) = NUMERIC(20, 6).
2. For item_id=1: 2.50 × 12.3400 = 30.850000. Six decimals because the product type has scale 6.
3. For item_id=2: 1.25 × 99.9999 = 124.999875. Same scale, the trailing digits are real source-of-truth precision.
4. For item_id=3: 100.00 × 0.0123 = 1.230000.
5. ROUND(x, 2) brings each total back to two decimals using the dialect's half-up rule (Postgres NUMERIC). 30.85, 125.00, 1.23.

Output.

item_id	line_total_raw	line_total_2dp
1	30.850000	30.85
2	124.999875	125.00
3	1.230000	1.23

Rule of thumb. Every multiplication of two NUMERICs grows the scale. After every product, decide whether you need the extra precision (FX desks: yes) or whether you want to round back to the presentation scale (dashboards: yes, do it). Never let the inferred scale silently propagate through three CTEs.

Worked example — ::numeric(p, s) cast vs ROUND

Detailed explanation. A ::numeric(10, 2) cast and a ROUND(x, 2) both produce a two-decimal value, but they take subtly different paths. The cast rounds using the dialect's NUMERIC half-rule (half-up on Postgres). The ROUND can be told to use a different rule (half-even on BigQuery via the third argument). For most workloads they coincide; on banker's-rounding pipelines they do not.

Question. Given x = 1.555 (NUMERIC(18, 6)), show three reductions to two decimals: x::numeric(10, 2), ROUND(x, 2), and ROUND(x::float8::numeric, 2). Explain why the third one might differ.

Input.

x
1.555
2.505
0.005

Code.

SELECT
    x,
    x::numeric(10, 2)              AS cast_2dp,
    ROUND(x::numeric, 2)           AS round_numeric_2dp,
    ROUND(x::float8::numeric, 2)   AS round_via_float_2dp
FROM (VALUES (1.555::numeric), (2.505::numeric), (0.005::numeric)) AS t(x);

Step-by-step explanation.

1. x::numeric(10, 2) — Postgres NUMERIC cast. Half-up rule. 1.555 → 1.56, 2.505 → 2.51, 0.005 → 0.01.
2. ROUND(x::numeric, 2) — same engine, same rule. Identical to the cast.
3. ROUND(x::float8::numeric, 2) — the value is first promoted to FLOAT (IEEE-754 binary). 0.005 cannot be represented exactly; the FLOAT version is something like 0.00499999.... ROUND then sees a value just below 0.005 and rounds down to 0.00.
4. The float-then-round form is an anti-pattern when the input is already NUMERIC — it introduces inexact arithmetic for no benefit. It is also the easiest way to accidentally invoke banker's rounding because Postgres ROUND on DOUBLE PRECISION uses half-even.
5. 2.505: NUMERIC cast → 2.51 (half-up). Via FLOAT → 2.50 (because 2.505 in FLOAT is 2.5049... which rounds to 2.50). Same input, two different answers.

Output.

x	cast_2dp	round_numeric_2dp	round_via_float_2dp
1.555	1.56	1.56	1.56
2.505	2.51	2.51	2.50
0.005	0.01	0.01	0.00

Rule of thumb. Never route NUMERIC values through FLOAT just to reach ROUND. Stay in the NUMERIC type system end-to-end. If banker's rounding is required, use BigQuery's ROUND(x, n, "ROUND_HALF_EVEN") or write the half-even rule explicitly with a CASE expression — do not rely on the dialect-dependent FLOAT ROUND default.

SQL interview question on the precision contract

A senior interviewer might frame: "You inherit a table where price is DOUBLE PRECISION and the finance team is reporting random one-cent variances every quarter. Walk through the migration to NUMERIC(p, s), the exact p and s you'd pick for prices, and how you'd backfill the historical values without re-introducing FLOAT round-off."

Solution Using a staged DOUBLE → NUMERIC migration

-- 1) Add the new column at full source-of-truth precision
ALTER TABLE prices ADD COLUMN amount_numeric numeric(18, 6);

-- 2) Backfill via CAST through TEXT to avoid FLOAT → NUMERIC round-off
UPDATE prices
   SET amount_numeric = (amount::text)::numeric(18, 6);

-- 3) Verify no rows lost precision
SELECT
    COUNT(*) FILTER (WHERE amount::text != amount_numeric::text) AS suspicious_rows
FROM prices;

-- 4) Once verified, drop the old FLOAT column and rename
ALTER TABLE prices DROP COLUMN amount;
ALTER TABLE prices RENAME COLUMN amount_numeric TO amount;

Step-by-step trace.

step	old type	new type	row count migrated	suspicious_rows
Step 1	DOUBLE	NUMERIC(18,6) added	0 (nullable)	n/a
Step 2	DOUBLE	NUMERIC(18,6) backfilled	1,000,000	0 (because TEXT roundtrip preserved the value)
Step 3	DOUBLE	NUMERIC(18,6)	n/a	0 ✓
Step 4	(dropped)	NUMERIC(18,6)	n/a	n/a

The critical step is the TEXT roundtrip in Step 2. A direct amount::numeric(18, 6) cast would route through FLOAT → NUMERIC, which inherits the FLOAT round-off. Routing through amount::text forces the engine to emit the decimal representation it would print to a human, then parse it back into NUMERIC at exact precision.

Output:

stage	before (DOUBLE)	after (NUMERIC)
0.1 + 0.2	0.30000000000000004	0.300000
1.05 × 100	105.00000000000001	105.000000
0.005 stored, displayed	0.0050000000000000001	0.005000

Why this works — concept by concept:

• NUMERIC(18, 6) as the source-of-truth precision — six decimals is wide enough for every normal currency penny-fraction and FX rate. Twelve integer digits supports trillions of any currency. The footprint is ~14 bytes per row in Postgres — negligible.
• TEXT roundtrip avoids FLOAT round-off — amount::text emits the printed-decimal form of the FLOAT (which is the shortest decimal that round-trips back to the same FLOAT). Casting that text to NUMERIC produces the exact decimal the FLOAT meant to represent, without re-introducing binary error.
• Verification before destruction — Step 3 compares text representations; any row where the FLOAT and NUMERIC disagree at the text level is a precision incident worth investigating. Zero suspicious rows means the migration was safe.
• Atomic swap — Step 4 drops the old column and renames the new one. From this point onward every INSERT lands in NUMERIC; the FLOAT round-off bug is permanently fixed.
• Cost — one full-table UPDATE (O(n) writes plus index churn). Schedule during a maintenance window for tables larger than a few million rows.

SQL
Topic — type-casting
Type-casting problems (SQL)

Practice →

SQL
Topic — integer
Integer arithmetic problems (SQL)

Practice →

---

4. Banker's rounding vs half-up — the regulatory split

banker's rounding (HALF_EVEN) cancels bias over millions of rows — half-up does not — and your auditor cares which one you used

The mental model in one line: half-up always rounds .5 away from zero, biasing every aggregate upward by half a unit per .5 row; banker's (half-even) rounds .5 to the nearest even integer, so half the .5 cases go up and half go down, cancelling the bias over a large enough sample. Once you can quote the bias argument, you can defend either rounding rule depending on what your jurisdiction's regulator demands.

Half-up (HALF_UP) on five inputs.

x	rounded
0.5	1
1.5	2
2.5	3
3.5	4
4.5	5

Every .5 rounds away from zero. Sum of inputs: 0.5 + 1.5 + 2.5 + 3.5 + 4.5 = 12.5. Sum of rounded: 1 + 2 + 3 + 4 + 5 = 15. The rounded sum is +2.5 higher than the raw sum — that is the bias.

Half-even (HALF_EVEN, banker's) on the same five inputs.

x	rounded
0.5	0
1.5	2
2.5	2
3.5	4
4.5	4

The .5 cases alternate up and down. Sum of rounded: 0 + 2 + 2 + 4 + 4 = 12. The rounded sum is -0.5 lower than the raw sum — much smaller bias, and over a uniform distribution of inputs the bias averages to zero exactly.

The revenue example.

Imagine a million rows where every row ends in .005. Half-up rounds every row up by 0.005. Total drift: 1,000,000 × 0.005 = $5,000. Half-even rounds about half the rows up and half down — total drift: ~0. That $5,000 is the gap between two auditors signing off the same dataset under two different rounding rules.

Which dialect uses which default?

• Postgres NUMERIC — half-up. ROUND(0.5::numeric) → 1.
• Postgres DOUBLE PRECISION — half-even (banker's). ROUND(0.5::float8) → 0.
• MySQL — half-away-from-zero for NUMERIC; banker's for FLOAT (mostly). Sometimes IEEE-754 inheriting from the underlying C math library — not stable across versions.
• SQL Server — half-up for ROUND. No native half-even.
• Snowflake — half-away-from-zero default. Configurable via session parameter.
• BigQuery — has an explicit rounding_mode argument: ROUND(x, n, "ROUND_HALF_AWAY_FROM_ZERO") or ROUND(x, n, "ROUND_HALF_EVEN").
• Oracle — half-away-from-zero. Banker's via BANKERS_ROUND extension on some Oracle versions.

IFRS, GAAP, MiFID II — which standard mandates which rule.

• GAAP (US) — silent on rounding mode; convention is half-up. Most US dashboards default here.
• IFRS (EU) — recommends half-even for monetary computations to avoid systematic bias. Many EU banks ship half-even by default.
• MiFID II (EU financial services) — requires bias-neutral rounding in transaction reporting. Banker's is the safe default; half-up may trigger a regulator audit on a high-frequency trading book.
• Tax authorities — vary. The IRS leans half-up; the German Bundesfinanzministerium leans truncate. Always check the jurisdiction-specific rule before locking the rounding mode.

The six rounding modes from Java BigDecimal.

Java's BigDecimal defined six rounding modes that have become the lingua franca for "what should .5 do?" Every modern SQL dialect supports at least the first three:

• HALF_UP — .5 away from zero. Default in most NUMERIC dialects.
• HALF_DOWN — .5 toward zero. Rare. Used by some legacy systems to bound overstatement.
• HALF_EVEN — .5 to the nearest even. Banker's. IEEE-754 default.
• CEILING — always toward +∞. Same as CEIL on the rounded boundary.
• FLOOR — always toward -∞. Same as FLOOR on the rounded boundary.
• UP — always away from zero. Symmetric CEIL — .4 → 1, -.4 → -1.

Worked example — half-up bias on a million-row revenue table

Detailed explanation. Simulate a million rows each at 1.005 and sum them under raw arithmetic, half-up rounding, and banker's rounding. The drift between half-up and raw is the regulator-visible bias.

Question. Given a synthetic table with a million rows of value 1.005, compute SUM(amount) (raw), SUM(ROUND(amount, 2)) under half-up, and SUM(ROUND(amount, 2)) under half-even. Show the drift.

Input — synthetic.

row	amount
1..1,000,000	1.005

Code.

WITH m AS (
    SELECT 1.005::numeric AS amount FROM generate_series(1, 1000000)
)
SELECT
    SUM(amount)                             AS raw_sum,
    -- half-up via Postgres NUMERIC ROUND
    SUM(ROUND(amount, 2))                   AS halfup_sum,
    -- half-even via Postgres DOUBLE PRECISION ROUND
    SUM(ROUND(amount::float8, 2)::numeric)  AS halfeven_sum
FROM m;

Step-by-step explanation.

1. SUM(amount) over a million 1.005 rows is exactly 1,005,000.00. NUMERIC arithmetic, no drift.
2. ROUND(1.005, 2) under Postgres NUMERIC half-up rounds to 1.01. Sum of a million 1.01 rows is 1,010,000.00 — drift +$5,000.
3. ROUND(1.005::float8, 2) under Postgres DOUBLE PRECISION uses half-even. 1.005 in FLOAT is actually 1.00499... due to binary representation, so it rounds to 1.00. Sum is 1,000,000.00 — drift -$5,000.
4. The half-even result happens to land under raw here because FLOAT round-off shifted 1.005 slightly below the half-way point. On a more realistic distribution where .5 cases truly land at exact half, half-even drift averages to zero.
5. The headline: half-up adds $5,000 of upward bias over a million rows. Banker's removes the bias on average. Both rules are "wrong" by $5,000 here only because all inputs are identical — a real revenue table with mixed inputs would see banker's drift near zero.

Output.

raw_sum	halfup_sum	halfeven_sum
1,005,000.00	1,010,000.00	1,000,000.00

Rule of thumb. When the input distribution is uniform across the rounding boundary, half-even cancels bias. When the input distribution has a known sign (e.g. tax rounded up benefits the taxpayer), pick the rule that aligns with the regulator's intent. Never let the dialect default decide for you — declare the rule in code review.

Worked example — half-even via Postgres DOUBLE PRECISION

Detailed explanation. Postgres has no native half-even for NUMERIC, but it inherits IEEE-754 half-even for DOUBLE PRECISION. The portable workaround is to cast through float8: ROUND(x::float8::numeric, 2). Use this only when the input is exact in FLOAT (small integers, simple decimals); otherwise the FLOAT round-off can flip the rounding direction unpredictably.

Question. Given the input set {0.5, 1.5, 2.5, 3.5, 4.5}, show ROUND(x) under NUMERIC (half-up) and under FLOAT (half-even). Note the cases where the two rules diverge.

Input.

x
0.5
1.5
2.5
3.5
4.5

Code.

SELECT
    x,
    ROUND(x::numeric)              AS halfup,
    ROUND(x::float8)::numeric      AS halfeven
FROM (VALUES (0.5), (1.5), (2.5), (3.5), (4.5)) AS t(x);

Step-by-step explanation.

1. ROUND(0.5::numeric) → half-up away from zero → 1.
2. ROUND(0.5::float8) → half-even toward nearest even → 0 (0 is the even integer at the tie).
3. ROUND(1.5::numeric) → half-up → 2. ROUND(1.5::float8) → half-even → 2 (already even, agrees).
4. ROUND(2.5::numeric) → half-up → 3. ROUND(2.5::float8) → half-even → 2 (2 is even, picks down).
5. ROUND(3.5::numeric) → half-up → 4. ROUND(3.5::float8) → half-even → 4 (agrees).
6. ROUND(4.5::numeric) → half-up → 5. ROUND(4.5::float8) → half-even → 4 (4 is even).
7. The pattern: on x.5 where the integer below is odd, both rules agree (round up). On x.5 where the integer below is even, half-up still rounds up but half-even rounds down. The split divides the bias by two on average.

Output.

x	halfup	halfeven
0.5	1	0
1.5	2	2
2.5	3	2
3.5	4	4
4.5	5	4

Rule of thumb. Use the x::float8::numeric workaround only for inputs that survive the FLOAT roundtrip exactly — small integers, halves, quarters. For arbitrary NUMERIC values, do not rely on FLOAT ROUND; instead, port the calculation to a dialect that supports explicit rounding_mode (BigQuery, modern Snowflake) or implement half-even as a CASE expression.

Worked example — banker's rounding on BigQuery via the third argument

Detailed explanation. BigQuery is the only major dialect to expose rounding_mode as a first-class argument to ROUND. Pass "ROUND_HALF_EVEN" and the engine implements the IEEE-754 banker's rule on NUMERIC (DECIMAL) values. No FLOAT detour, no surprises.

Question. Compute ROUND(2.5, 0, mode) and ROUND(2.5, 0, mode) under both modes on BigQuery. Show the syntax.

Input.

x
0.5
1.5
2.5
3.5
4.5

Code.

-- BigQuery only
SELECT
    x,
    ROUND(x, 0, "ROUND_HALF_AWAY_FROM_ZERO") AS halfup,
    ROUND(x, 0, "ROUND_HALF_EVEN")           AS halfeven
FROM UNNEST([0.5, 1.5, 2.5, 3.5, 4.5]) AS x;

Step-by-step explanation.

1. The third argument is a string literal naming the rounding mode. BigQuery accepts "ROUND_HALF_AWAY_FROM_ZERO" and "ROUND_HALF_EVEN".
2. Mode ROUND_HALF_AWAY_FROM_ZERO is the SQL standard "half-up away from zero" rule. 0.5 → 1, -0.5 → -1. Same as Postgres NUMERIC default.
3. Mode ROUND_HALF_EVEN is the IEEE-754 banker's rule. 0.5 → 0, 1.5 → 2, 2.5 → 2, 3.5 → 4, 4.5 → 4.
4. The two arguments solve the portability problem: BigQuery is the single dialect where you can switch between rules without a FLOAT detour or a CASE expression.
5. For dialects that lack the argument, the portable workaround is (FLOOR(x * 2 + (CASE WHEN FLOOR(x) % 2 = 0 THEN 0 ELSE 1 END)) / 2) — a hand-rolled half-even on NUMERIC. Ugly but auditable.

Output.

x	halfup	halfeven
0.5	1	0
1.5	2	2
2.5	3	2
3.5	4	4
4.5	5	4

Rule of thumb. If your pipeline runs on BigQuery and the rounding rule matters for compliance, always pass the explicit rounding_mode argument. Relying on the default makes the code brittle against future BigQuery version changes and against ports to other dialects. Codify the rule in the SQL.

SQL interview question on choosing the rounding mode

A senior interviewer might ask: "Your finance team is migrating a million-row revenue table from US GAAP to IFRS, and the auditor asks why the new quarterly total is $5,000 lower than the old. Walk through the rounding-mode argument and how you'd defend either total to the auditor."

Solution Using a bias analysis on the dataset

-- Show the bias of half-up vs half-even on the actual data
WITH bounded AS (
    SELECT
        amount,
        ROUND(amount, 2)              AS halfup_amount,
        ROUND(amount::float8, 2)::numeric AS halfeven_amount
    FROM revenue
)
SELECT
    SUM(amount)                            AS raw_total,
    SUM(halfup_amount)                     AS halfup_total,
    SUM(halfeven_amount)                   AS halfeven_total,
    SUM(halfup_amount)   - SUM(amount)     AS halfup_drift,
    SUM(halfeven_amount) - SUM(amount)     AS halfeven_drift
FROM bounded;

Step-by-step trace.

metric	raw	halfup	halfeven	halfup drift	halfeven drift
Q3 2025 (US GAAP)	$9,995,000.00	$10,000,000.00	$9,995,002.50	+$5,000.00	+$2.50
Q4 2025 (US GAAP)	$10,050,000.00	$10,055,000.00	$10,050,001.30	+$5,000.00	+$1.30
Q1 2026 (IFRS)	$11,200,000.00	$11,205,000.00	$11,200,003.10	+$5,000.00	+$3.10

The trace makes the auditor's question answerable: half-up systematically over-reports revenue by $5,000 per quarter because every .5 row rounds up. Half-even drifts by single-digit dollars because the .5 cases alternate. The IFRS half-even total is the more accurate number; the GAAP half-up total was the artificially-inflated one.

Output:

period	raw_total	halfup_total	halfeven_total
Q3 2025	9,995,000.00	10,000,000.00	9,995,002.50
Q4 2025	10,050,000.00	10,055,000.00	10,050,001.30
Q1 2026	11,200,000.00	11,205,000.00	11,200,003.10

Why this works — concept by concept:

• Half-up biases upward — every .5 row contributes +0.005 of drift. The drift accumulates linearly with row count. The total is always ≥ raw_total (or equal if no row ends in .5).
• Half-even cancels bias — .5 rows alternate up/down based on the parity of the integer below. On a uniform distribution the drift averages to zero. The total tracks raw_total to within single-digit dollars regardless of row count.
• The auditor's question is answered by the drift columns — halfup_drift shows the upward bias; halfeven_drift shows the residual noise. The IFRS migration removed $5,000 of overstatement, not introduced a $5,000 understatement.
• Defending either total — under GAAP, the half-up total was the official number; under IFRS, the half-even total is the official number. Both are correct under their respective rules; only one is bias-neutral.
• Cost — single full-scan aggregate (O(n)). The FLOAT roundtrip in halfeven_amount is one extra cast per row — negligible compared to the disk read.

SQL
Topic — conditional-aggregation
Conditional aggregation problems (SQL)

Practice →

SQL
Topic — percentage-calculation
Percentage calculation problems (SQL)

Practice →

---

5. Dialect cheat sheet — Postgres, MySQL, SQL Server, Snowflake, BigQuery, Oracle

Six dialects, four functions, one portable subset — write ROUND + FLOOR + CEILING, wrap TRUNC behind a macro

The mental model in one line: ROUND, FLOOR, and CEILING work on every major dialect with identical syntax; CEIL and TRUNC do not — Oracle accepts only CEIL, SQL Server only CEILING, MySQL spells TRUNC as TRUNCATE, and SQL Server has no TRUNC at all (use ROUND(x, n, 1)). Memorise the portable subset and macro-wrap the rest.

The dialect matrix in one table.

Function	Postgres	MySQL	SQL Server	Snowflake	BigQuery	Oracle
ROUND	ROUND(x, n)	ROUND(x, n)	ROUND(x, n)	ROUND(x, n)	ROUND(x, n, mode)	ROUND(x, n)
FLOOR	FLOOR(x)	FLOOR(x)	FLOOR(x)	FLOOR(x[, n])	FLOOR(x)	FLOOR(x)
CEIL / CEILING	CEIL or CEILING	CEIL or CEILING	CEILING only	CEIL or CEILING	CEIL or CEILING	CEIL only
TRUNC	TRUNC(x, n)	TRUNCATE(x, n)	ROUND(x, n, 1)	TRUNC(x, n)	TRUNC(x, n)	TRUNC(x, n)
Default .5 rule	NUMERIC: half-up · DOUBLE: half-even	half-away-from-zero	half-up	half-away-from-zero	half-away-from-zero (configurable)	half-away-from-zero

Portability rules — what works everywhere.

• ROUND(x, n) — works on every dialect. Default rule varies; codify if it matters.
• FLOOR(x) — works on every dialect. No precision argument on most.
• CEILING(x) — works on every dialect. CEIL(x) works on every dialect except SQL Server.
• TRUNC(x, n) — works on Postgres, Snowflake, BigQuery, Oracle. Broken on MySQL (use TRUNCATE). Broken on SQL Server (use ROUND(x, n, 1)).

The "round to nearest 5 cents" problem across six dialects.

A pricing engine wants prices rounded to the nearest $0.05. The idiom is ROUND(x * 20) / 20 — multiply by the reciprocal of the rounding unit, round to nearest integer, divide back. The expression itself is portable. The hidden gotcha is the .5 rule on the intermediate ROUND — pick a dialect where the rule is explicit if compliance matters.

Worked dialect snippets.

-- Postgres / Snowflake / BigQuery / Oracle / MySQL
SELECT
    price,
    ROUND(price * 20) / 20 AS price_to_5c
FROM products;

-- SQL Server explicit truncate variant
SELECT
    price,
    ROUND(price * 20, 0, 0) / 20 AS price_to_5c  -- 0 = round, 1 = truncate
FROM products;

-- BigQuery with explicit banker's rounding
SELECT
    price,
    ROUND(price * 20, 0, "ROUND_HALF_EVEN") / 20 AS price_to_5c
FROM products;

Dialect-specific footnotes.

• Postgres — ROUND is half-up on NUMERIC, half-even on DOUBLE PRECISION. TRUNC accepts negative n for tens / hundreds. No rounding_mode argument.
• MySQL — TRUNCATE is the keyword (not TRUNC). ROUND follows half-away-from-zero on NUMERIC; on FLOAT inherits IEEE-754 (mostly half-even, sometimes not — not stable between versions). Avoid FLOAT for money.
• SQL Server — ROUND(x, n, function) where function = 0 means round, function = 1 means truncate. No native TRUNC. CEILING only; no CEIL.
• Snowflake — ROUND(x, n[, mode]) where mode is the third arg. Half-away-from-zero default. FLOOR(x, n) accepts precision. TRUNC is alias for TRUNCATE.
• BigQuery — ROUND(x, n, "ROUND_HALF_AWAY_FROM_ZERO") or "ROUND_HALF_EVEN". Only dialect with named modes. FLOOR, CEIL, TRUNC all standard.
• Oracle — TRUNC(x, n) works on both numbers and dates (TRUNC(my_date, 'MM') truncates to month start). CEIL only. ROUND is half-away-from-zero default; BANKERS_ROUND exists on newer versions.

Worked example — port the "round to nearest 5 cents" recipe

Detailed explanation. Take the canonical pricing rule "round prices to the nearest $0.05" and port it from Postgres to MySQL, SQL Server, Snowflake, BigQuery, and Oracle. The math is the same; the dialect syntax shifts subtly on the rounding-mode hint.

Question. Given a products table with raw prices, write the "round to nearest 5 cents" SELECT for each of the six dialects. Note where the SQL Server and BigQuery variants diverge.

Input.

product_id	price
1	1.99
2	1.02
3	1.025
4	1.075

Code.

-- Postgres / MySQL / Snowflake / Oracle (default half-up / half-away-from-zero)
SELECT product_id, ROUND(price * 20) / 20 AS price_5c FROM products;

-- SQL Server
SELECT product_id, ROUND(price * 20, 0) / 20 AS price_5c FROM products;

-- BigQuery with explicit half-up
SELECT product_id,
       ROUND(price * 20, 0, "ROUND_HALF_AWAY_FROM_ZERO") / 20 AS price_5c
FROM products;

-- BigQuery with explicit banker's
SELECT product_id,
       ROUND(price * 20, 0, "ROUND_HALF_EVEN") / 20 AS price_5c
FROM products;

Step-by-step explanation.

1. For product_id=1 (1.99): 1.99 × 20 = 39.8. ROUND(39.8) = 40. 40 / 20 = 2.00. All dialects agree.
2. For product_id=2 (1.02): 1.02 × 20 = 20.4. ROUND(20.4) = 20. 20 / 20 = 1.00. Round-down to the nearest nickel.
3. For product_id=3 (1.025): 1.025 × 20 = 20.5. Half-up → 21 → 1.05. Banker's → 20 (because 20 is even) → 1.00. Same input, two different prices.
4. For product_id=4 (1.075): 1.075 × 20 = 21.5. Half-up → 22 → 1.10. Banker's → 22 (22 is even) → 1.10. Both rules agree here because the tie is to an even number.
5. The dialect choice matters specifically on the half-cases. If your pricing engine rounds millions of .5-nickel cases per day, half-up adds bias; half-even cancels it.

Output (half-up).

product_id	price	price_5c
1	1.99	2.00
2	1.02	1.00
3	1.025	1.05
4	1.075	1.10

Output (half-even).

product_id	price	price_5c
1	1.99	2.00
2	1.02	1.00
3	1.025	1.00
4	1.075	1.10

Rule of thumb. "Round to nearest unit U" is universally written as ROUND(x / U) * U or equivalently ROUND(x * (1/U)) / (1/U). The expression ports across every dialect. The hidden dialect-specific decision is the .5 rule, which you should make explicit in BigQuery via the third arg and audit in code review in every other dialect.

Worked example — TRUNC on Oracle for both dates and numbers

Detailed explanation. Oracle's TRUNC is overloaded across numbers and dates. TRUNC(123.456, 1) truncates to one decimal (123.4). TRUNC(my_date, 'MM') truncates to the first day of the month. Other dialects split these into separate functions (TRUNC for numbers, DATE_TRUNC for dates). Junior engineers porting code between Oracle and Postgres often miss the polymorphism.

Question. Show TRUNC on a number and on a date in Oracle. Then show the Postgres equivalents.

Input.

value
123.456
999.999
2026-06-15

Code.

-- Oracle
SELECT
    TRUNC(123.456, 1)        AS num_trunc_1dp,  -- 123.4
    TRUNC(123.456, 0)        AS num_trunc_int,  -- 123
    TRUNC(999.999, -1)       AS num_trunc_tens, -- 990
    TRUNC(DATE '2026-06-15', 'MM') AS date_trunc_month,  -- 2026-06-01
    TRUNC(DATE '2026-06-15', 'YY') AS date_trunc_year   -- 2026-01-01
FROM dual;

-- Postgres equivalents
SELECT
    TRUNC(123.456, 1)                   AS num_trunc_1dp,
    TRUNC(123.456, 0)                   AS num_trunc_int,
    TRUNC(123.456, -1)                  AS num_trunc_tens,
    DATE_TRUNC('month', DATE '2026-06-15') AS date_trunc_month,
    DATE_TRUNC('year',  DATE '2026-06-15') AS date_trunc_year;

Step-by-step explanation.

1. Oracle TRUNC(123.456, 1) drops everything past the first decimal → 123.4. Postgres TRUNC(123.456, 1) is identical.
2. Oracle TRUNC(123.456, 0) drops the decimal entirely → 123. Postgres identical.
3. Oracle TRUNC(123.456, -1) truncates to the tens place → 120. Postgres identical.
4. Oracle TRUNC(DATE '2026-06-15', 'MM') truncates to the first day of the month → 2026-06-01. Postgres has no overload; you must call DATE_TRUNC('month', ...).
5. Oracle TRUNC(DATE '2026-06-15', 'YY') truncates to the first day of the year → 2026-01-01. Postgres: DATE_TRUNC('year', ...).
6. The portability tax: Oracle ports to Postgres by renaming TRUNC(date, fmt) → DATE_TRUNC(fmt, date) (note the argument-order swap, too).

Output.

function	Oracle	Postgres
number trunc 1dp	123.4	123.4
number trunc int	123	123
number trunc tens	120	120
date trunc month	2026-06-01	2026-06-01
date trunc year	2026-01-01	2026-01-01

Rule of thumb. When porting Oracle code, grep for every TRUNC( and check whether the first argument is a number or a date. Numbers translate cleanly; dates require DATE_TRUNC with a different argument order. Make a one-time pass on the migration and the pattern never bites again.

Worked example — SQL Server's ROUND(x, n, function) truncate flag

Detailed explanation. SQL Server has no TRUNC function. Instead, ROUND accepts a third argument: 0 (default) means round, 1 means truncate. This is the legacy quirk every analyst porting code from Postgres to SQL Server discovers the hard way.

Question. Show ROUND(123.456, 1), ROUND(123.456, 1, 0), and ROUND(123.456, 1, 1) on SQL Server. Explain each.

Input.

x	n
123.456	1
999.999	2
-7.891	2

Code.

-- SQL Server
SELECT
    ROUND(123.456, 1)        AS round_default,   -- 123.500 (round)
    ROUND(123.456, 1, 0)     AS round_explicit,  -- 123.500 (round)
    ROUND(123.456, 1, 1)     AS round_truncate,  -- 123.400 (truncate)
    ROUND(-7.891, 2)         AS round_neg,       -- -7.890 (round)
    ROUND(-7.891, 2, 1)      AS truncate_neg;    -- -7.890 (truncate toward zero)

Step-by-step explanation.

1. ROUND(123.456, 1) — default third arg 0 means "round." Result: 123.500 (note: SQL Server preserves the decimal scale of the source, hence the trailing zero).
2. ROUND(123.456, 1, 0) — explicit "round" mode. Same as default.
3. ROUND(123.456, 1, 1) — explicit "truncate" mode. Drops everything past the first decimal. Result: 123.400.
4. ROUND(-7.891, 2) — half-up on negative input. -7.891 is closer to -7.89 than to -7.90, so the result is -7.890.
5. ROUND(-7.891, 2, 1) — truncate toward zero on negative input. Drops the .001 → -7.890. Same as TRUNC on Postgres.
6. The third-arg pattern is SQL Server-specific. Code porting between SQL Server and any other dialect must rewrite ROUND(x, n, 1) as TRUNC(x, n) (Postgres / Snowflake / BigQuery / Oracle) or TRUNCATE(x, n) (MySQL).

Output.

function	result
ROUND(123.456, 1)	123.500
ROUND(123.456, 1, 0)	123.500
ROUND(123.456, 1, 1)	123.400
ROUND(-7.891, 2)	-7.890
ROUND(-7.891, 2, 1)	-7.890

Rule of thumb. Treat SQL Server's ROUND(x, n, 1) as a portability landmine. Whenever you see it in a SQL Server codebase, write a short comment "/* truncate, not round — port to TRUNC(x, n) on other dialects */". Future migrations will thank you.

Worked example — Snowflake's FLOOR(x, n) precision argument

Detailed explanation. Snowflake extends FLOOR with an optional second argument: FLOOR(x, n) floors to the n-th decimal place. Other dialects need the manual FLOOR(x * 10^n) / 10^n workaround. The extension is convenient on Snowflake but does not port.

Question. Floor a price to two decimals (round down to the cent) on Snowflake and on Postgres. Show both forms.

Input.

x
12.349
99.001
-7.891

Code.

-- Snowflake
SELECT x, FLOOR(x, 2) AS floor_to_cent FROM (VALUES (12.349), (99.001), (-7.891)) AS t(x);

-- Postgres (and most dialects) — manual workaround
SELECT x, FLOOR(x * 100) / 100 AS floor_to_cent FROM (VALUES (12.349), (99.001), (-7.891)) AS t(x);

Step-by-step explanation.

1. Snowflake FLOOR(12.349, 2) floors to the second decimal place → 12.34.
2. Snowflake FLOOR(99.001, 2) → 99.00.
3. Snowflake FLOOR(-7.891, 2) → -7.90 (FLOOR toward -∞ on negatives — the "wrong direction" for refund rounding; use TRUNC instead).
4. Postgres workaround: FLOOR(12.349 × 100) / 100 = FLOOR(1234.9) / 100 = 1234 / 100 = 12.34. Same result.
5. The Snowflake form is more readable but does not port. Wrap it behind a dbt macro floor_to(x, n) so the implementation can swap per dialect without changing call sites.

Output.

x	floor_to_cent
12.349	12.34
99.001	99.00
-7.891	-7.90

Rule of thumb. Any time a dialect ships a one-off extension to a standard function (Snowflake FLOOR(x, n), BigQuery ROUND(x, n, mode)), wrap it in a portable macro at the dbt layer. The macro becomes the contract; the dialect-specific implementation becomes the body. New dialect ports become a one-line macro edit instead of a grep-and-replace across the warehouse.

SQL interview question on dialect portability

A senior interviewer might pose: "You are consolidating Postgres, MySQL, and Snowflake into a single dbt project. Rewrite the four rounding idioms — round to 2 dp, floor to the cent, ceil to the dollar, truncate at integer — so the same SQL compiles on all three. Where does it fail and how do you fix it with a macro?"

Solution Using a dbt-style portable macro layer

-- Portable layer using only ROUND, FLOOR, CEILING — works on all six dialects
SELECT
    price,
    -- 1) Round to 2 dp — universal
    ROUND(price, 2)                            AS price_2dp,
    -- 2) Floor to the cent — universal via × 100 trick
    FLOOR(price * 100) / 100                   AS price_floor_cent,
    -- 3) Ceil to the dollar — universal (CEIL on Postgres/MySQL/Snowflake/BQ/Oracle; CEILING on SQL Server)
    CEILING(price)                             AS price_ceil_dollar,
    -- 4) Truncate to integer — dialect-specific; here Postgres/Snowflake/BQ/Oracle
    TRUNC(price)                               AS price_trunc_int
FROM products;

Step-by-step trace.

price	ROUND 2dp	FLOOR cent	CEILING $	TRUNC int
1.245	1.25	1.24	2	1
99.999	100.00	99.99	100	99
-7.501	-7.50	-7.51	-7	-7
0.005	0.01 (half-up) / 0.00 (banker's)	0.00	1	0

The trace highlights two portability hazards: TRUNC is the function name on Postgres/Snowflake/BigQuery/Oracle but TRUNCATE on MySQL and ROUND(x, n, 1) on SQL Server. Wrap it behind a truncate_to_int(x) macro and the call site stays identical across dialects.

Output:

price	price_2dp	price_floor_cent	price_ceil_dollar	price_trunc_int
1.245	1.25	1.24	2	1
99.999	100.00	99.99	100	99
-7.501	-7.50	-7.51	-7	-7
0.005	0.01	0.00	1	0

Why this works — concept by concept:

• Portable subset is ROUND + FLOOR + CEILING — these three names compile identically on every major dialect. Build the bulk of your reporting layer on this trio.
• CEILING beats CEIL on portability — SQL Server accepts only CEILING. Every other major dialect accepts both. Default to CEILING in shared code.
• FLOOR × 100 / 100 trick — works on every dialect because it relies only on integer arithmetic and FLOOR. The Snowflake extension FLOOR(x, n) is more readable but breaks the macro contract.
• TRUNC needs a macro — the function name varies across MySQL, SQL Server, and the rest. A {{ truncate_to_n(x, n) }} dbt macro encapsulates the dialect switch and lets call sites stay declarative.
• Rounding mode codified per dialect — half-up on Postgres NUMERIC, half-away-from-zero on Snowflake/Oracle/MySQL, half-up on SQL Server, configurable on BigQuery. If the rule matters, BigQuery is the safest target; everywhere else, audit.
• Cost — every rounding function is O(1) per row. The performance difference between ROUND and TRUNC is unmeasurable. The macro layer adds zero runtime cost — it is purely a compile-time string substitution in dbt.

SQL
Topic — bucketing
Bucketing problems (SQL)

Practice →

SQL
Topic — comparison
Comparison problems (SQL)

Practice →

---

Cheat sheet — rounding recipes

• Round to 2 decimals. ROUND(x, 2) — universal. On BigQuery, declare the rule: ROUND(x, 2, "ROUND_HALF_EVEN") for banker's, ROUND(x, 2, "ROUND_HALF_AWAY_FROM_ZERO") for half-up. The sql round to 2 decimals idiom every finance report ships.
• Round down to the nearest cent. FLOOR(x * 100) / 100 for non-negative inputs. For mixed signs (refunds), prefer TRUNC(x * 100) / 100 — TRUNC rounds toward zero on both signs.
• Round up to the nearest dollar. CEIL(x) on every dialect except SQL Server; CEILING(x) on all dialects including SQL Server. Use CEILING in shared code.
• Truncate to 2 decimals (no rounding). TRUNC(x, 2) on Postgres / Snowflake / BigQuery / Oracle. TRUNCATE(x, 2) on MySQL. ROUND(x, 2, 1) on SQL Server.
• Round to nearest 5 cents. ROUND(x * 20) / 20. The × 20 is the reciprocal of the rounding unit (0.05). For nearest dime: × 10. For nearest quarter: × 4.
• Round to nearest hundred / thousand. ROUND(x, -2) rounds to the hundreds place. ROUND(x, -3) rounds to thousands. Negative n is supported on Postgres / Snowflake / BigQuery / Oracle / MySQL; not on SQL Server (use ROUND(x / 100, 0) * 100).
• Half-even on Postgres NUMERIC (workaround). ROUND(x::float8::numeric, 2) — routes through DOUBLE PRECISION to invoke IEEE-754 half-even. Only safe when the FLOAT roundtrip preserves the value exactly; otherwise FLOAT round-off breaks the rule.
• Banker's rounding on BigQuery (native). ROUND(x, 2, "ROUND_HALF_EVEN") — first-class. Only dialect with a named rounding-mode argument.
• Compute pages needed for pagination. CEILING(rows * 1.0 / page_size). The * 1.0 forces float division on dialects with integer-division defaults (MySQL, SQL Server).
• Bucket continuous values into bands. FLOOR(x / band_width) * band_width — produces left-closed buckets at multiples of band_width. For histograms, joining the result to a bands table is the canonical pattern.
• Avoid SUM(ROUND(...)) bias. Always write ROUND(SUM(amount), 2) — sum at full precision, round once. The reverse accumulates rounding bias linearly with row count.
• Never store money as FLOAT. Use NUMERIC(p, s) or DECIMAL(p, s). NUMERIC(18, 6) is a sensible raw-storage default; NUMERIC(10, 2) is a sensible presentation default.
• Audit .5 cases. Run SELECT COUNT(*) FROM t WHERE x * 100 = FLOOR(x * 100) + 0.5 to count rows that sit exactly on a .5 boundary. If the count is non-trivial, your rounding-mode choice is regulator-visible.

Frequently asked questions

Why does ROUND(2.5) return 2 sometimes and 3 other times?

Because the dialect default rounding rule varies. Postgres NUMERIC uses half-up — ROUND(2.5) returns 3. Postgres DOUBLE PRECISION uses IEEE-754 half-even (banker's) — ROUND(2.5::float8) returns 2 because 2 is the nearest even integer. MySQL and SQL Server default to half-away-from-zero; Snowflake and Oracle default to half-away-from-zero; BigQuery requires an explicit rounding_mode argument. If the answer matters for compliance, codify the rule explicitly: pass the BigQuery third arg, route Postgres NUMERIC through DOUBLE for banker's, or write a CASE expression to implement half-even on dialects without native support.

What's the difference between FLOOR and TRUNC on negative numbers?

FLOOR(x) always rounds toward minus infinity — for negative non-integers that means more negative. TRUNC(x) always rounds toward zero — for negative non-integers that means less negative. So FLOOR(-2.4) = -3 but TRUNC(-2.4) = -2. The difference is invisible on positive inputs (both give 2 for 2.4) and lethal on mixed-sign columns like refunds, FX deltas, and GL adjustments. The rule of thumb: when your business logic says "reduce the magnitude," use TRUNC; when it says "always round down on the number line," use FLOOR. Code review every FLOOR that touches a column that could be negative.

Should I store money as FLOAT, NUMERIC, or MONEY?

NUMERIC(p, s) (also spelt DECIMAL(p, s)) — every time. NUMERIC is ANSI-standard, exact base-10 fixed-precision, arbitrary precision. FLOAT / DOUBLE PRECISION are IEEE-754 binary types that cannot exactly represent decimal fractions like 0.1 or 0.2 — 0.1 + 0.2 = 0.30000000000000004. The cumulative error over a million rows is visible in any quarterly close. MONEY (SQL Server, Postgres) is a fixed 4-decimal type that predates ANSI NUMERIC and is now a portability landmine — use NUMERIC instead. A sensible raw-storage default is NUMERIC(18, 6); a sensible presentation default is NUMERIC(10, 2). Round at the SELECT, not at write time.

How do I round to the nearest 0.05 in SQL?

ROUND(x * 20) / 20. The math: multiply by the reciprocal of the rounding unit (1 / 0.05 = 20), round to the nearest integer, divide back. The same idiom generalises to any rounding unit U: ROUND(x / U) * U or equivalently ROUND(x * (1/U)) / (1/U). For nearest dime ($0.10): ROUND(x * 10) / 10. For nearest quarter ($0.25): ROUND(x * 4) / 4. For nearest minute (60 seconds) on a duration: ROUND(seconds / 60) * 60. The hidden hazard is the .5 rule on the intermediate ROUND — on BigQuery, pass the explicit mode; on every other dialect, audit which default applies.

Why does MySQL use TRUNCATE instead of TRUNC?

Historical accident from the MySQL 3.x era when the reserved-word table excluded TRUNC to avoid conflict with the TRUNCATE TABLE statement. Every other major dialect (Postgres, Snowflake, BigQuery, Oracle) calls the function TRUNC; MySQL alone uses TRUNCATE(x, n). The behaviour is identical — drop digits toward zero — but the name is not portable. SQL Server has no TRUNC at all and instead overloads ROUND(x, n, 1) (the third arg 1 means truncate). The portable answer is a dbt macro that translates a single call-site {{ truncate_to(x, n) }} into the dialect-specific function name at compile time. Document the macro in the project README so new engineers do not write naked TRUNC against a MySQL target.

Does Postgres ROUND use banker's rounding?

On NUMERIC (DECIMAL) inputs — no, Postgres uses half-up (away from zero). ROUND(0.5::numeric) returns 1, ROUND(2.5::numeric) returns 3. On DOUBLE PRECISION (float8) inputs — yes, Postgres inherits IEEE-754 half-even. ROUND(0.5::float8) returns 0, ROUND(2.5::float8) returns 2. The split is the source of confusion for engineers porting code from Postgres NUMERIC to Postgres DOUBLE without realising the type change also changed the rounding rule. The canonical workaround for half-even on NUMERIC is ROUND(x::float8::numeric, 2) — but the cast through FLOAT introduces binary round-off and is only safe for values that survive the roundtrip exactly. For deterministic banker's rounding on Postgres NUMERIC, hand-roll the half-even rule in a CASE expression or migrate the calculation to BigQuery's ROUND(x, n, "ROUND_HALF_EVEN").

Practice on PipeCode

• Drill the aggregation practice library → for the SUM(ROUND) vs ROUND(SUM) family of probes.
• Rehearse on arithmetic expression problems → when the interviewer asks for ROUND, FLOOR, CEIL, TRUNC by hand.
• Sharpen type-casting drills → for ::numeric(p, s) rounding vs ::int truncation.
• Layer the math practice library → for the ROUND(x * 20) / 20 and "nearest unit" patterns.
• Stack the financial-data library → for currency rounding and bias-analysis probes.
• For the broader surface, read top data engineering interview questions →.
• Stack the prerequisites with the only 5 skills you need to become a data engineer →.
• Sharpen the dialect axis with the SQL for data engineering interviews course →.
• For long-form schema craft, work through data modelling for DE interviews →.

Pipecode.ai is Leetcode for Data Engineering — every rounding recipe above ships with hands-on practice rooms where you write the `ROUND(x, 2)` presentation step, the `FLOOR(x * 100) / 100` cent-rounding idiom, and the `NUMERIC(18, 6)` raw-storage contract against real graded inputs. PipeCode pairs every reading with 450+ DE-focused problems and a real-time scoring engine, so you never have to wonder whether your fix to `sql round` actually behaves the same on Postgres NUMERIC as on BigQuery half-even.

Practice aggregation now →
Arithmetic expression drills →