lesson 20 Joins & sets · intermediate

Ratios, ROUND & NULLIF

Calculate safe ratios and round your results.

the idea

A ratio needs a defined numerator and denominator. Multiply by 100.0 before dividing so SQLite does not use integer division, ROUND the result for display, and wrap the denominator in NULLIF(…, 0) so an empty group gives NULL instead of a misleading number.

Dataset: Product / SaaS Analytics

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Count sent and accepted per workspace. Run this. COUNT(*) is invitations sent; COUNT(accepted_at) counts those with an acceptance timestamp.

   step-1.sql Run

   sql›

   SELECT account_id, COUNT(*) AS sent, COUNT(accepted_at) AS accepted
   FROM invitations
   GROUP BY account_id
   LIMIT 15;

2. 2

   Divide without breaking on zero. Run this. NULLIF turns a zero denominator into NULL so you do not divide by zero; 100.0 avoids integer division.

   step-2.sql Run

   sql›

   SELECT
     100.0 * 3 / NULLIF(0, 0) AS undefined_rate,
     ROUND(100.0 * 3 / 7, 2) AS rate;

3. 3

   Put it together. The lesson's pattern in one query.the pattern

   example.sql Run

   sql›

   SELECT account_id, COUNT(*) AS sent, COUNT(accepted_at) AS accepted
   FROM invitations
   GROUP BY account_id;

common mistake

COUNT(accepted_at) / COUNT(*) is integer division in SQLite and returns 0 for most accounts.