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 margin14. 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.