lesson 20 Joins & sets · intermediate

Ratios, ROUND & NULLIF

Calculate safe ratios and round your results.

the idea

A rate is numerator divided by denominator. Use a real number (100.0) to avoid integer division. NULLIF(x,0) turns a zero denominator into NULL; ROUND controls displayed precision. Preserve that NULL instead of calling an undefined rate zero.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

SELECT
  account_id,
  ROUND(100.0 * SUM(CASE WHEN status='success' THEN 1 ELSE 0 END)/NULLIF(COUNT(*),0),2) AS success_pct
FROM ai_requests
GROUP BY account_id;

Watch for: COUNT(*) in a LEFT JOIN makes an account with no requests appear to have one request.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   Count successes and attempts. A success rate needs both numerator and denominator. NULLIF protects against dividing by zero.

   step-1.sql Run

   sql›

   SELECT
     account_id,
     SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) AS successes,
     COUNT(*) AS attempts
   FROM ai_requests
   GROUP BY account_id;

2. 2

   Turn counts into a rounded percent. Multiply by 100.0 for a real division, wrap the denominator in NULLIF(..., 0), then ROUND to two decimals.

   step-2.sql Run

   sql›

   SELECT
     account_id,
     ROUND(100.0 * SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0), 2) AS success_pct
   FROM ai_requests
   GROUP BY account_id;