Percentages and Rates in SQL
Rates are everywhere: conversion, completion, usage. Each is a numerator over a denominator, scaled to a percentage.
A rate is just a fraction with a story: how many of the whole match some condition. The shape is always numerator ÷ denominator × 100. The numerator is a conditional count; the denominator is the total.
One thing to watch in some databases: dividing two integers can throw away the fraction (so 17 / 24 becomes 0). Writing the multiplier as 100.0 keeps the pattern portable, and round() tidies the result.
select round(100.0 * converted / total, 1) as conversion_pct
from funnelSchema · Garden ShopTable · orders6 columns · 24 rows
One row per order. Unshipped orders have a null shipped_date.
What share of orders have shipped? Extend the starter so it also returnspct_shipped: the percentage of all orders whose status is'shipped', rounded to one decimal place.
- Columns: total_orders, shipped_orders, pct_shipped.
- Rows: 1 summary row.
- 17 of 24 orders shipped, so pct_shipped is 70.8.