## The High-Performance SQL Blog

# Calculating Expected Value vs. Actual Results for Super Bowl Contenders

Calculating Expected Value vs. Actual Results for Super Bowl ContendersWith the Super Bowl quickly approaching, what better time than now to explore expected value and results in SQL! Using Las Vegas’s money lines for the year, and Pro Football Reference’s Win Probability Values, we can calculate the expected value of a $100 bet on each Denver Broncos and Carolina Panthers game this season.

### Expected Value Breakdown

The formula for expected value is defined as:

The x-variables represent a value, and the p-variables represent the probability of that value. Let’s say that we have tables for each of the two teams that holds the weeks of their games, their win probabilities, their money lines, and the results. So it looks something like this:

From this table, we already have the probabilities that we need to calculate expected value. But we don’t have the necessary x-variable values. To do that, we need to convert our money lines into actual money values.

### Converting Money Lines into Money

A positive money line represents how much you win by betting $100. So if the money line is +141, then winning a bet of $100 will give you a net sum of $241.

On the other hand, a negative money line is how much you have to spend to make $100. Thus, winning a $100 bet on a -227 money line would give you: 100 + 100 * 100.00 / 227 → $144.05

Keep in mind that we need a value for if the team wins and for if the team loses. We just showed how to compute the value for the former case. In the latter case, the value will always be -100 since you’re only losing the money you bet.

The probabilities we’ll use to multiply by the losing values are equivalent to 1 - P(Win). Let’s translate this into SQL, so we have all the relevant pieces to calculate expected value in one place.

If our table of Broncos stats is creatively named **broncos_stats**, then the query is:

withbroncos_moneyline_valueas(

select

week

, date

, win_probability::decimal(8,3)

, 1-win_probability::decimal(8,3)aslose_probability

, moneyline

,case

whenmoneyline<0

then100.0*-100.0/moneyline

else100.0+moneyline

endasvalue_for_win

,-100asvalue_for_loss

,result

from

broncos_stats

orderby

week

)

The resulting table will look like:

### Calculating Expected Value

Now that we have probabilities and corresponding values, we can compute the expected value following the formula shown earlier. For good measure, we’ll include a baseline of 0 to help differentiate positive from negative expected values.

select

*

, win_probability*value_for_win+

lose_probability*value_for_lossasexpected_value

,0asbaseline

from

broncos_moneyline_value

where

dateisnotnull-- accounts for bye weeks

And the resulting chart shows the expected value for a $100 bet each week!

### Plotting Actual Result with Expected Value

Let’s take things one step further, and factor in the actual result of the games to show how we performed relative to the expected value. Since we don’t have a definitive result for the Super Bowl yet, we’ll just set that value to be 0.

select

*

, win_probability*value_for_win+

lose_probability*value_for_lossasexpected_value

,0asbaseline

,case

whenresult='win'

thenvalue_for_win

whenresult=''pending

then0

elsevalue_for_loss

endasactual_value

from

broncos_moneyline_value

where

dateisnotnull-- accounts for bye weeks

After applying this to the data for the Broncos and Panthers, we can see how they’ve both performed this year relative to their expected values in each game.

At the moment, the odds seem to favor the Panthers enough to skew the Broncos’ expected value to be $44.30 against the Panthers’ -$9.61. But given the Panthers’ continued success in the face of low expected values, it might be prudent to apply the maxim of “Any Given Sunday” to expected value.