Lesson 11.2

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 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.

Pattern

select round(100.0 * converted / total, 1) as conversion_pct
from funnel

Once you have a rate per group, averaging those rates doesn't give the overall rate: see why averaging averages gives the wrong result.

Schema · Garden Shop Table · orders 6 columns · 24 rows

Table · orders

One row per order. Unshipped orders have a null shipped_date.

6 columns · 24 rows

order_id int customer_id int order_date date shipped_date date status text coupon_code text

Your task

What share of orders have shipped? Extend the starter so it also returns pct_shipped: the percentage of all orders whose status is 'shipped', rounded to one decimal place.