Get startedGet started for free

Comparing groups

1. Comparing groups

Reports are commonly used for the purpose of comparing the performance of different groups. There are a number of ways to compare groups through a report, but the approach you take depends on the type of metric used in the comparison.

2. Types of metrics

In general, there are two types of metrics: volume metrics and efficiency metrics.

3. Volume metrics

Volume metrics represent the sum of individual objects and scale with size.

4. Volume metrics

For example, revenue is the sum of all dollars made,

5. Volume metrics

while population is the sum of all individuals who live in the area.

6. Percent of total calculation

When comparing a volume metric across groups, the percent of total calculation is a solid approach. Let's say we have this table, which shows points scored by team and player for a basketball league. If we want to compare points across all teams, we can easily query this to show points per team.

7. Percent of total calculation

It's clear what the top team is and we can see an obvious distribution, but its not obvious how much larger team 1 is vs team 4 without running some extra calculations on the fly.

8. Percent of total calculation

To make the comparison more intuitive, we can add a new field called percent of total. Let's build this step by step. Step one is to calculate the total points across all teams by using window calculation across the full table. From here, we can update the query to calculate the percent of total by dividing points over total points, as shown.

9. Percent of total calculation

The new results provide more context then a report showing only the points.

10. Percent of total calculation

We can take this a layer deeper. Let's say we want to see the percent of points scored per player for their respective team. We can set this up in a similar fashion, but this time we must partition by team_id.

11. Percent of total calculation

The query results clearly state how many points each player has relative to their team.

12. Efficiency metrics

The other type of metric are efficiency metrics. These metrics do not scale with size and are typically represented as a ratio.

13. Efficiency metrics

Some examples of efficiency metrics include profit margin

14. Efficiency metrics

and revenue per customer. Efficiency metrics are useful to compare performance without being biased by the size of each group. Using our previous example, if team 1 played 20 total matches, while team B only played 8 matches, then comparing total points scored is not a fair comparison. Rather, we should be comparing a ratio, such as points per game.

15. Performance index

When comparing efficiency metrics, it does not make sense to use percent of a total since there is no total in this case. Instead, we can create what is called a performance index, which compares each row's performance to a benchmark, typically the average or median for the entire group.

16. Performance index

For example, this table shows points and games for each team. We want to identify how each team performs relative to the entire league in terms of points per game.

17. Performance index

The first step is to create a points per game calculation for each team, which is a basic query. The second step is to calculate the points per game for the entire league using a window function. Here, league_ppg is the benchmark. Since we are aggregating a ratio, we need two window functions, one for each side of the fraction.

18. Performance index

Finally, we can compare each team's points per game vs the league average with the performance index. To do this, simply take the ratio of the previous two steps. Pay attention to parenthesis here.

19. Performance index

20. Query time!

Let's put these concepts to action!

Create Your Free Account

or

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.