When summarizing statistics across multiple categories, analysts often have to decide between using weighted and unweighted averages.
An unweighted average is essentially your familiar method of taking the mean. Let's say 0% of users logged into my site on Day 1, and 100% of users logged in on Day 2. The unweighted average for the 2 days combined would be (0% + 100%)/2 = 50%.
Weighted averages take the sample size into consideration. Let's say in the example above, there was only 1 user enrolled on Day 1 and 4 users enrolled on Day 2 - making a total of 5 users over the 2 days. The weighted average is 0% * (1/5) + 100% * (4/5) = 80%. Typically, users want to calculate weighted averages as it prevents skewing from categories with smaller sample sizes.
If we want to add a row with a weighted average, we can accomplish this via SQL as shown in the example below (note that this example leverages Redshift syntax).
Let's walk through an example of how to calculate each of these measures! Let's say our original table, t1, contains the following data:
Here is how to calculate the weighted average. To add an extra 'Total' row, I used a SQL Union all.
, (round(perc_purchases_over_10_dollars * 100, 2) || '%') as perc_purchases_over_10_dollars