Lesson 12 of 13 · 26 min
Developing KPIs
By the end of this lesson you can write a precise definition for a KPI (numerator, denominator, population, period) and implement it in SQL so that it is a ratio of totals over the correct set of rows.
A key performance indicator is a number the business tracks over time to judge performance. Almost every KPI is a ratio: something divided by something. Before writing SQL, write the definition with four parts:
| part | question it answers | example (cancellation rate) |
|---|---|---|
| Numerator | what is being counted or summed? | orders with status 'cancelled' |
| Denominator | relative to what? | all orders placed |
| Population | which rows are in scope at all? | all channels, all customers |
| Period & date | which time window, by which date? | orders placed in the month, by ordered_at |
Two people who agree on these four parts will get the same number; two who don't will argue about whose query is “wrong” when both are right for their own definition.
This lesson, its 5 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons