Integer Division in Metrics - SQL

Which replacement preserves each employee's fractional rate before AVG is evaluated?
Answer: AVG(CAST(completed_tasks AS DECIMAL(12,4)) / NULLIF(assigned_tasks, 0)) Whenever you see SQL averaging a ratio, ask yourself: at what point does division happen? If integer division occurs before AVG, you lose all fractional precision — for example, 3 / 5 returns 0 in integer arithmetic, not 0.6. The goal here is to preserve each employee's individual rate before aggregation. Option A is correct because it casts completed_tasks to DECIMAL(12,4) before the division. This forces floating-point arithmetic at the row level, so each employee's rate like 0.6 or 0.833... is computed correctly. Then AVG averages those accurate fractional values — giving every employee equal weight in the final mean. Option B divides two separate averages: AVG(completed_tasks) / AVG(assigned_tasks). This calculates a weighted mean (effectively SUM(completed) / SUM(assigned)), not an equal-weight average of individual rates. An employee with 1000 tasks would dominate over one with 10. Option C looks tempting, but the CAST is applied after the integer division happens: completed_tasks / NULLIF(assigned_tasks, 0) is already evaluated as integer math (truncating to 0 or 1), and then cast to decimal. Casting after truncation doesn't recover the lost precision. Option D is SUM(completed) / SUM(assigned), which is a weighted ratio, not an arithmetic mean of individual rates — same conceptual flaw as B. Study tip: In SQL, precision must be introduced before the dividing operation. If you cast after division, you're just converting an already-truncated integer. Always cast the numerator (or denominator) first.
Which expression computes the team's resolution rate without losing the fractional portion before conversion?
Answer: CAST(resolved_tickets AS DECIMAL(8,4)) / total_tickets When working with integer division in SQL, the key question to ask yourself is: at what point does the division happen? If both operands are integers when the division occurs, the database truncates the result before you ever get a chance to convert it. Consider the values $7$ and $12$ . True division gives $7 \div 12 = 0.5833...$ But integer division gives $7 \div 12 = 0$ — the fractional part is gone permanently. No amount of casting afterward can recover it. Option B is correct because it casts resolved_tickets to DECIMAL(8,4) before the division happens. Now the engine is dividing a decimal by an integer, which produces a decimal result: $7.0000 \div 12 = 0.5833$ . The fractional portion is preserved exactly as intended. Option A is the classic trap: it performs integer division first ( $7 \div 12 = 0$ ), then casts that already-truncated zero to DECIMAL(8,4), giving you $0.0000$ — precise but wrong. Option C makes the same mistake and compounds it by casting to INTEGER explicitly before multiplying by 1.0, which still starts from $0$ . Option D casts both columns to INTEGER, which they already are, so nothing changes — you still get integer division and a result of $0$ . The pattern to remember: cast before you divide, not after. Converting at least one operand to a decimal or numeric type prior to the division operator is the only way to force floating-point arithmetic. If you cast the result, you're just decorating an already-broken value.
Which revision fixes the fractional-rate problem while retaining the zero-denominator rule?
Answer: CASE WHEN total_checks = 0 THEN 0.0 ELSE 1.0 * passed_checks / total_checks END When you see integer columns in a division expression, your first instinct should be: what type does this division produce? In dialects with integer division, dividing two integers truncates the result — so $3 / 8$ becomes $0$ , not $0.375$ . The fix must force floating-point arithmetic before the division happens, not after. Option A works because multiplying passed_checks by 1.0 promotes it to a float first, so the subsequent division by total_checks becomes float division: $1.0 \times 3 / 8 = 0.375$ . The CASE still returns 0.0 when total_checks = 0, preserving the zero-denominator rule perfectly. Option B is the classic trap. CAST(passed_checks / total_checks AS DECIMAL(8,4)) casts after the division, so integer division already produced 0 — you're just casting 0 to 0.0000. The damage is already done before the cast runs. Option C makes the same mistake as B, just moving the cast outside the entire CASE expression. The inner division is still integer, so you again cast a truncated result rather than preventing truncation. Option D is a red herring: CAST(total_checks AS INTEGER) is a no-op since total_checks is already an integer. This changes nothing about how the division is computed and still produces integer results. The key study tip: type promotion must happen before the operation, not after. Multiplying by 1.0 (or casting a numerator to float/decimal before dividing) is the reliable pattern. Casting the result of integer division is always too late.
Which expression produces approximately $58.33$ rather than rounding an already truncated value?
Answer: ROUND(100.0 * returned_units / NULLIF(sold_units, 0), 2) Whenever you see a percentage calculation in SQL involving integer columns, the critical question is: at what point does the division happen, and does it produce a decimal result? Most SQL dialects perform integer division when both operands are integers, meaning $7 / 12 = 0$ — not $0.5833$ . Option A, ROUND(100.0 * returned_units / NULLIF(sold_units, 0), 2), is correct because 100.0 is a noninteger literal. Multiplying first — $100.0 \times 7 = 700.0$ — promotes the result to a numeric type, so the subsequent division $700.0 / 12 = 58.3333...$ is decimal division. ROUND(..., 2) then produces $58.33$ . The NULLIF guard prevents division by zero cleanly. Option B fails immediately: returned_units / NULLIF(sold_units, 0) performs integer division first, yielding $0$ , and then $100 \times 0 = 0$ . Rounding zero gives $0.00$ — you're just rounding an already-ruined value. Option C uses 100 (an integer literal), not 100.0. So 100 * returned_units gives the integer $700$ , and $700 / 12$ is still integer division, yielding $58$ (truncated). ROUND(58, 2) returns $58.00$ , not $58.33$ . Option D wraps the same broken expression from C in a CAST, which doesn't recover the lost decimal precision — it just formats the already-truncated $58$ as $ $58.00$ . The pattern to remember: **promote to decimal before dividing**, not after. Using a float/numeric literal like 100.0 or an explicit CAST on the numerator before division is the reliable technique for percentage calculations on integer columns.
Which additional fixture is most likely to expose the missing pre-division cast directly?
Answer: Use $1/2$ and assert that the result is numerically equal to $0.5$ . When testing for a missing decimal cast, you need to find a fixture where integer division and true division produce different results. That's the core diagnostic question: does this input expose a discrepancy between the two behaviors? In integer division, any fractional result is truncated toward zero. So $1 \div 2 = 0$ under integer rules, but $1.0 \div 2 = 0.5$ under decimal rules. If your assertion expects $0.5$ and the cast is missing, the query returns $0$ — the test fails, exposing the bug. That's exactly why B is the correct answer: $1/2$ is the simplest input that produces a non-integer quotient, making it impossible for truncating integer division to accidentally return the correct value. The existing fixtures — $0/5$ , $5/5$ , and $10/5$ — all divide evenly, so integer division and decimal division yield identical results. The cast's removal goes undetected. The new fixture must break that pattern. A uses $2/1$ , which equals $2$ under both integer and decimal division. No discrepancy exists, so no bug is exposed. C uses $0/1$ , which returns $0$ regardless of cast behavior — zero divided by anything is zero. D uses $6/3 = 2$ , another clean division that passes under both regimes, just like the original suite. The strategic takeaway: when auditing tests for a cast-related bug, always ask "does this input produce a remainder?" Only non-integer quotients can distinguish integer division from decimal division. Look for numerators that aren't multiples of the denominator.
Which expression calculates the required overall rate while avoiding both integer truncation and unintended equal weighting of campaigns?
Answer: 1.0 * SUM(clicks) / NULLIF(SUM(impressions), 0) When calculating a ratio across grouped data, you need to ask two questions: (1) should I aggregate first or compute per-row, and (2) will integer division silently truncate my result? These two concerns pull the answer choice selection apart cleanly. The true click-through rate is total clicks across all campaigns divided by total impressions across all campaigns — a pooled ratio, not an average of per-campaign ratios. Answer C, 1.0 * SUM(clicks) / NULLIF(SUM(impressions), 0), nails both requirements. Multiplying SUM(clicks) by 1.0 promotes the integer to a float before division occurs, giving you decimal precision. NULLIF(SUM(impressions), 0) safely returns NULL instead of triggering a divide-by-zero error. Here's why each alternative fails. A computes a per-row ratio first (clicks / impressions), then averages those ratios — this equally weights every campaign regardless of size, which is exactly the "unintended equal weighting" the question warns against. A campaign with 10 impressions influences the result as much as one with 10 million. B is conceptually correct in pooling with SUM, but it performs integer division before the CAST — the truncation already happened, so casting afterward just dresses up a wrong number. D has both problems: it divides per row (wrong aggregation level) and casts after integer division has already truncated each row's result. As a study tip, remember the order-of-operations trap: type casting or floating-point promotion must happen before the division operator is evaluated, not after. Ask yourself, "at what point does division actually execute?"
Which statement best evaluates the proposal and supplies an appropriate correction?
Answer: It handles zero safely but truncates nonzero ratios; use COALESCE(1.0 * event_count / NULLIF(user_count, 0), 0.0). When working with arithmetic in SQL, you need to think about two separate problems: division by zero and integer truncation. A complete solution must handle both. The proposal COALESCE(event_count / NULLIF(user_count, 0), 0.0) correctly addresses division by zero — NULLIF(user_count, 0) returns NULL when user_count is zero, causing the division to produce NULL, which COALESCE then replaces with 0.0. That part works. The fatal flaw is that in a dialect with integer division, event_count / NULLIF(user_count, 0) is still integer divided by integer, producing a truncated integer before COALESCE ever sees it. For example, $7 / 3$ yields $2$ , not $2.333...$ The 0.0 fallback in COALESCE doesn't retroactively fix the already-truncated result. The correct fix, answer D, multiplies by 1.0 first: COALESCE(1.0 * event_count / NULLIF(user_count, 0), 0.0). Multiplying event_count by the decimal literal 1.0 promotes the entire expression to decimal arithmetic, preserving the fractional result. Answer A is wrong because COALESCE is not a type-conversion mechanism — it simply returns the first non-NULL value and has no effect on how earlier expressions are evaluated. Answer B misidentifies the problem as being only in the zero case and wrongly suggests casting to a wider integer, which still truncates. Answer C inverts the actual bugs — the proposal does not raise on zero (that's handled correctly), and casting user_count to integer changes nothing since it's already an integer. A useful rule of thumb: whenever you want decimal output from integer columns, force decimal promotion early — multiply by 1.0 or cast the numerator before dividing, not after.
Which outcome and migration conclusion are correct?
Answer: SQL Server returns $2$ , MySQL returns $2.5$ , so an explicit decimal cast improves portability. When working with arithmetic expressions across database platforms, you need to ask: does this operator behave the same way on every system? Division is a classic portability trap. In SQL Server, dividing two integers uses integer division — remainders are discarded. So 5 / 2 returns $2$ , not $2.5$ . In MySQL, the / operator always performs floating-point division regardless of operand types, so 5 / 2 returns $2.5$ . This difference means the same query produces different results on each platform, which is exactly what answer B describes — making it the correct choice. The fix is straightforward: cast at least one operand to a decimal type (e.g., CAST(5 AS DECIMAL) / 2) so both systems perform floating-point division consistently. A is wrong because the two systems do not return the same result — SQL Server returns $2$ while MySQL returns $2.5$ , so the expression is not portable as-is. C reverses the behavior of each platform. SQL Server returns $2$ (integer), not $2.5$ , and MySQL returns $2.5$ , not $2$ . Additionally, MySQL's DIV operator performs integer division, so switching to DIV would force integer division on MySQL — it would not improve portability toward the decimal result. D is wrong because both systems do not return $2.5$ ; SQL Server returns an integer $2$ , which is a computational difference, not merely a display-formatting issue. For migration or cross-platform SQL work, always verify how each target database handles arithmetic operators — especially integer division — and use explicit casts to enforce the intended numeric behavior.
Which expression best implements the required percentage?
Answer: 100.0 * successful_jobs / NULLIF(attempted_jobs, 0) When working with arithmetic in SQL, operator precedence and data type promotion are the two forces you must track simultaneously. SQL evaluates expressions left-to-right among operators of equal precedence, and the data type of the first operand in a chain often determines whether the entire calculation stays in integer arithmetic. Option C, 100.0 * successful_jobs / NULLIF(attempted_jobs, 0), is correct because multiplying by the floating-point literal 100.0 immediately promotes the entire expression to a decimal context. From that point forward, 7 / 12 never happens as integer division — instead you get $100.0 \times 7 \div 12 = 58.33...$ with fractional precision. The NULLIF also correctly returns NULL when attempted_jobs is zero. Option A fails because the multiplication 100 * successful_jobs happens first in integer space, giving 700, and then that integer is divided by the integer from NULLIF. Integer division truncates: $700 \div 12 = 58$ , not $58.33$ . The outer CAST only converts the already-truncated integer to decimal — too late. Option B makes the same sequencing mistake but earlier: successful_jobs / NULLIF(attempted_jobs, 0) is evaluated first as integer division ( $7 \div 12 = 0$ ), and the CAST then converts that 0 to decimal. Multiplying by 100 gives 0.00, a completely wrong result. Option D explicitly casts to INTEGER, ensuring truncation. This satisfies neither the fractional precision requirement nor any safety improvement. Remember: In SQL, data type promotion travels left-to-right. If you need decimal output, introduce a floating-point value (like 1.0 or 100.0) as early as possible in the expression chain.
What is the correct diagnosis and remediation?
Answer: Changing the destination type affects future formatting only; the table must be reloaded using a cast before the source division. Whenever you see a question about integer division in SQL, the key concept to anchor on is when the calculation happens relative to where the result is stored. SQL evaluates expressions using the data types of the source operands — not the destination column. Here's what's actually happening: when SQL Server divides two integers, it performs integer division immediately, discarding any remainder before the result ever reaches the destination column. So 5 / 2 yields 2, not 2.5. That truncated integer is what gets written into nightly_metrics. Altering the destination column to DECIMAL(10,2) afterward changes how future inserts will be formatted, but the already-stored truncated integers remain as-is — they don't get reinterpreted or recalculated. The correct fix, answer D, is to reload the table using an explicit cast in the source query, such as CAST(order_count AS DECIMAL(10,2)) / shopper_count, so the division itself produces a decimal result before any data is written. A is wrong because SQL doesn't reinterpret stored integers when you change a column's type — existing data stays as the values that were written. B is wrong and actually makes things worse: casting to integers before querying the reporting table would further discard precision, not restore it. C is wrong because the source calculation does not retain fractions — that's the whole problem. Integer division discards the fractional part at calculation time, and there's no "type-inference rule" toggling that behavior. The study tip: always ask yourself, "At what data type does the arithmetic actually execute?" In SQL, the answer is determined by the operands, not the destination — so fix the source, not just the storage.

Start this quiz