ship ratio is 0 and pct is off for reno, SQLite

Update: I think it's the join. Each order repeats once per line, so COUNT(*) counts lines, not orders. Maybe COUNT(DISTINCT o.id) fixes it? And 1.0 * for the ratio...

I think it's the join. Each order repeats once per line

Yes, your update is right. The 6 is lines, not orders, and COUNT(o.shipped_at) is also counting per line. Reno has 2 shipped orders that together have 4 lines, so you get 4/6 = 66.7. The pending orders are not the cause. They only look wrong because the shipped ones have more lines.

COUNT(DISTINCT o.id) fixes the denominator. For the numerator, don't use COUNT(DISTINCT o.shipped_at), because two orders shipped the same day collapse into one. Count ids instead:

COUNT(DISTINCT o.id) AS orders,
COUNT(DISTINCT CASE WHEN o.shipped_at IS NOT NULL THEN o.id END) AS shipped,
1.0 * COUNT(DISTINCT CASE WHEN o.shipped_at IS NOT NULL THEN o.id END)
    / COUNT(DISTINCT o.id) AS ship_ratio

The 0 is integer division, as you guessed: 3/4 gives 0 when both sides are integers. 1.0 * in front is enough.

One more thing. If "empty shipped_at" means an empty string and not NULL, COUNT will count it as a date. Run SELECT typeof(shipped_at), COUNT(*) FROM orders GROUP BY 1; and see. After that, run your report a second time and check that reno gives 4 orders and 2 shipped.

Aggregate docs: https://www.sqlite.org/lang_aggfunc.html

COUNT(DISTINCT CASE WHEN o.shipped_at IS NOT NULL THEN o.id END)

That works, but I'd rather not. Sum the qty per order in a subquery first, then join... maybe that's cleaner? Then COUNT(shipped_at) stays plain.

SELECT o.warehouse,
       COUNT(*) AS orders,
       COUNT(o.shipped_at) AS shipped,
       1.0 * COUNT(o.shipped_at) / COUNT(*) AS ship_ratio,
       SUM(t.units) AS units
FROM orders o
JOIN (SELECT order_id, SUM(qty) AS units
      FROM order_lines GROUP BY order_id) t ON t.order_id = o.id
GROUP BY o.warehouse;

The subquery fixes the fan-out, and I'd use it over the DISTINCT version for the units. But I wouldn't swap it in as-is, because the inner JOIN drops any order that has no rows in order_lines.

Your hand count for reno was 4 orders. If one of them has no lines yet (a pending order, say), it disappears from COUNT(*), and the ratio is then computed over a smaller set without any error. You'd get the right-looking number for the wrong population. The first report hid this too, since it also used an inner join.

FROM orders o
LEFT JOIN (SELECT order_id, SUM(qty) AS units
           FROM order_lines GROUP BY order_id) t ON t.order_id = o.id

Then SUM(t.units) ignores the NULLs, and COUNT(*) is really orders. Compare SELECT COUNT(*) FROM orders against the sum of your orders column. If they differ, that's the gap. (The typeof check from #3 still applies, since an empty string counts as a date.)