TryBench
Crash-Proof Division
Prevent query crashes and broken dashboards by guarding against division by zero and preserving decimal precision.
Why this matters
Rates like conversion are everywhere in business. But dividing by zero breaks some databases, and whole numbers can round down to 0.
Whole-Number Division Drops Decimals
In SQLite, dividing whole numbers drops the decimals, so 5 / 10 gives 0. Multiply by 1.0 first to get 0.5.
In real life: A calculator that says 5 divided by 10 is 0 would be useless. In SQLite, whole numbers do that. Multiplying by 1.0 changes them to decimals.
Dividing by Zero Can Crash
Dividing by zero crashes some databases, like Postgres. NULLIF(col, 0) turns zero into blank, so the answer is NULL instead.
In real life: Sharing 10 sweets between 0 children has no answer. NULLIF changes the zero into "unknown", so the database gives a blank instead of an error.
Show 0 or Leave It Blank
A campaign with 0 views has no real rate. COALESCE can show 0, or you can leave it blank. Choose what your report needs.
In real life: A campaign with no visitors has no conversion rate. Showing 0 is neat for a chart. Leaving it blank is more honest. You decide.
Your practice task
Calculate marketing campaign conversion rates (signups divided by page views). Multiply signups by 1.0 to preserve decimal accuracy, protect against division-by-zero crashes on zero-traffic campaigns with NULLIF, show 0 for unvisited campaigns using COALESCE, and name the output conversion_rate.
How to do it
- Think about what goes wrong when page_views is 0.
- Make sure the division keeps decimals.
- Protect the division, then decide what to show when there are no views.
- Press Run proof and read the checklist.
Practice it for free in the app. You get instant checks that show what you got right and what to fix. A lesson takes about two minutes and you can try as many times as you like.
Quick reference: Zero-Division Guard & Decimal Precision
Essential across PostgreSQL, SQLite, MySQL, and SQL Server to avoid crashes and integer truncation.
SELECT campaign_name, COALESCE(1.0 * signups / NULLIF(page_views, 0), 0) AS conversion_rate FROM campaigns;
More lessons in this course
- SQL Window Function Running Total ExampleCompute running day-by-day totals without complex self-joins using SQL Window Functions.
- SQL CTE Example: Find Above-Average CustomersBreak a hard question into named steps with WITH, then compare each group to the overall average.
- SQL ROW_NUMBER PARTITION BY: Top N Per GroupUse ROW_NUMBER with PARTITION BY to find the top record inside every group.