4
$\begingroup$

As I'm sure will be apparent to most here, I'm not a statistician or a programmer, but one of my hobbies is politics. I've created a (very simple) USA electoral college simulation, seen below.

The idea is that if you think you have a good estimate of the probabilities of the different states, you can simply run a simulation to get the odds of victory for the candidates. Technically, the way this works is, say you assume the R candidate has a 45% of victory in, say, Wisconsin. The SQL rand() function should return a value (roughly speaking) less than or equal to 45% roughly 45% of the time. So over a million rows you'd expect for rand() to produce around 450,000 rows that are 45% or less. So for each row where this is "true" I assign electoral college points, but I do it for the same randomized number across all states.

I guess my question is, does this simple SQL model make sense or am I fooling myself into thinking that if I can get good state probabilities that this will actually produce a decent overall probability? The actual state odds I have in there right now are of course completely fictitious as I've been playing with different numbers.

--run once
Create Table database.dbo.Numbers(
[Num] [int] NOT NULL,
CONSTRAINT [PK_Numbers] PRIMARY KEY CLUSTERED 
([Num] ASC ))

Insert into database.dbo.Numbers
select top 10000000 row_number() over (order by s1.name)
from sysobjects s1 cross join sysobjects s2, sysobjects s3
--run many times
--input the probability of R win for each state

declare @AK as float
Set @AK = .99

declare @AL as float
Set @AL = .99

declare @AR as float
Set @AR = .999

declare @AZ as float
Set @AZ = .4

declare @CA as float
Set @CA = .01

declare @CO as float
Set @CO = .5

declare @CT as float
Set @CT = .01

declare @DE as float
Set @DE = .01

declare @FL as float
Set @FL = .5

declare @GA as float
Set @GA = .5

declare @HI as float
Set @HI = .01

declare @IA as float
Set @IA = .6

declare @ID as float
Set @ID = .99

declare @IL as float
Set @IL = .01

declare @IN as float
Set @IN = .8

declare @KS as float
Set @KS = .8

declare @KY as float
Set @KY = .7

declare @LA as float
Set @LA = .7

declare @MA as float
Set @MA = .01

declare @MD as float
Set @MD = .01

declare @ME as float
Set @ME = .01

declare @MI as float
Set @MI = .45

declare @MN as float
Set @MN = .5

declare @MO as float
Set @MO = .8

declare @MS as float
Set @MS = .9

declare @MT as float
Set @MT = .99

declare @NC as float
Set @NC = .55

declare @ND as float
Set @ND = .99

declare @NE as float
Set @NE = .7

declare @NH as float
Set @NH = .1

declare @NJ as float
Set @NJ = .1

declare @NM as float
Set @NM = .3

declare @NV as float
Set @NV = .5

declare @NY as float
Set @NY = .01

declare @OH as float
Set @OH = .6

declare @OK as float
Set @OK = .8

declare @OR as float
Set @OR = .99

declare @PA as float
Set @PA = .5

declare @RI as float
Set @RI = .1

declare @SC as float
Set @SC = .99

declare @SD as float
Set @SD = .99

declare @TN as float
Set @TN = .8

declare @TX as float
Set @TX = .55

declare @UT as float
Set @UT = .99

declare @VA as float
Set @VA = .1

declare @VT as float
Set @VT = .2

declare @WA as float
Set @WA = .1

declare @WI as float
Set @WI = .6

declare @WV as float
Set @WV = .99

declare @WY as float
Set @WY = .9

declare @DC as float
Set @DC = .01

declare @ME1 as float
set @ME1 = .2

declare @ME2 as float
set @ME2 = .8

declare @NE1 as float
set @NE1 = .99

declare @NE2 as float
set @NE2 = .5

declare @NE3 as float
set @NE3 = .99



Select F.WinLose,cast(F.Count as float) / cast(F.Sumtotal as float) as Chance

from


(

Select E.WinLose,E.Count,sum(E.Count) over () as Sumtotal

from


(Select D.WinLose,count(D.Winlose) as Count

from


(Select C.Num,(case when (C.[R-AK]+C.[R-AL]+C.[R-AR]+C.[R-AZ]+C.[R-CA]+C.[R-CO]+C.[R-CT]+C.[R-DC]+C.[R-DE]+C.[R-FL]+C.[R-GA]
+C.[R-HI]+c.[R-IA]+c.[R-ID]+c.[R-IL]+c.[R-IN]+c.[R-KS]+c.[R-KY]+c.[R-LA]+c.[R-MA]+c.[R-MD]+c.[R-ME]+c.[R-MI]+c.[R-MN]+c.[R-MO]
+c.[R-MS]+c.[R-MT]+c.[R-NC]+c.[R-ND]+c.[R-NE]+c.[R-NH]+c.[R-NJ]+c.[R-NM]+c.[R-NV]+c.[R-NY]+c.[R-OH]+c.[R-OK]+c.[R-OR]+c.[R-PA]+c.[R-RI]+
c.[R-SC]+c.[R-SD]+c.[R-TN]+c.[R-TX]+c.[R-UT]+c.[R-VA]+c.[R-VT]+c.[R-WA]+c.[R-WI]+c.[R-WV]+c.[R-WY]+c.[R-NE1]+c.[R-NE2]+c.[R-NE3]+c.[R-ME1]+c.[R-ME2])

 >= 269 then 'Win' else 'NotWin' end) as WinLose


from



(Select B.Num,(case when B.AK = 'TRUE' then 3 else 0 end) as 'R-AK',
(case when B.AL = 'TRUE' then 9 else 0 end) as 'R-AL',
(case when B.AR = 'TRUE' then 6 else 0 end) as 'R-AR',
(case when B.AZ = 'TRUE' then 11 else 0 end) as 'R-AZ',
(case when B.CA = 'TRUE' then 55 else 0 end) as 'R-CA',
(case when B.CO = 'TRUE' then 9 else 0 end) as 'R-CO',
(case when B.CT = 'TRUE' then 7 else 0 end) as 'R-CT',
(case when B.DE = 'TRUE' then 3 else 0 end) as 'R-DE',
(case when B.FL = 'TRUE' then 29 else 0 end) as 'R-FL',
(case when B.GA = 'TRUE' then 16 else 0 end) as 'R-GA',
(case when B.HI = 'TRUE' then 4 else 0 end) as 'R-HI',
(case when B.IA = 'TRUE' then 6 else 0 end) as 'R-IA',
(case when B.ID = 'TRUE' then 4 else 0 end) as 'R-ID',
(case when B.IL = 'TRUE' then 20 else 0 end) as 'R-IL',
(case when B.[IN] = 'TRUE' then 11 else 0 end) as 'R-IN',
(case when B.KS = 'TRUE' then 6 else 0 end) as 'R-KS',
(case when B.KY = 'TRUE' then 8 else 0 end) as 'R-KY',
(case when B.LA = 'TRUE' then 8 else 0 end) as 'R-LA',
(case when B.MA = 'TRUE' then 11 else 0 end) as 'R-MA',
(case when B.MD = 'TRUE' then 10 else 0 end) as 'R-MD',
(case when B.MI = 'TRUE' then 16 else 0 end) as 'R-MI',
(case when B.MN = 'TRUE' then 10 else 0 end) as 'R-MN',
(case when B.MO = 'TRUE' then 10 else 0 end) as 'R-MO',
(case when B.MS = 'TRUE' then 6 else 0 end) as 'R-MS',
(case when B.MT = 'TRUE' then 3 else 0 end) as 'R-MT',
(case when B.NC = 'TRUE' then 15 else 0 end) as 'R-NC',
(case when B.ND = 'TRUE' then 3 else 0 end) as 'R-ND',
(case when B.NH = 'TRUE' then 4 else 0 end) as 'R-NH',
(case when B.NJ = 'TRUE' then 14 else 0 end) as 'R-NJ',
(case when B.NM = 'TRUE' then 5 else 0 end) as 'R-NM',
(case when B.NV = 'TRUE' then 6 else 0 end) as 'R-NV',
(case when B.NY = 'TRUE' then 29 else 0 end) as 'R-NY',
(case when B.OH = 'TRUE' then 18 else 0 end) as 'R-OH',
(case when B.OK = 'TRUE' then 7 else 0 end) as 'R-OK',
(case when B.[OR] = 'TRUE' then 7 else 0 end) as 'R-OR',
(case when B.PA = 'TRUE' then 20 else 0 end) as 'R-PA',
(case when B.RI = 'TRUE' then 4 else 0 end) as 'R-RI',
(case when B.SC = 'TRUE' then 9 else 0 end) as 'R-SC',
(case when B.SD = 'TRUE' then 3 else 0 end) as 'R-SD',
(case when B.TN = 'TRUE' then 11 else 0 end) as 'R-TN',
(case when B.TX = 'TRUE' then 38 else 0 end) as 'R-TX',
(case when B.UT = 'TRUE' then 6 else 0 end) as 'R-UT',
(case when B.VA = 'TRUE' then 13 else 0 end) as 'R-VA',
(case when B.VT = 'TRUE' then 3 else 0 end) as 'R-VT',
(case when B.WA = 'TRUE' then 12 else 0 end) as 'R-WA',
(case when B.WI = 'TRUE' then 10 else 0 end) as 'R-WI',
(case when B.WV = 'TRUE' then 5 else 0 end) as 'R-WV',
(case when B.WY = 'TRUE' then 3 else 0 end) as 'R-WY',
(case when B.DC = 'TRUE' then 3 else 0 end) as 'R-DC',

(case when B.NE = 'TRUE' then 2 else 0 end) as 'R-NE',
(case when B.ME = 'TRUE' then 2 else 0 end) as 'R-ME',
(case when B.ME1 = 'TRUE' then 1 else 0 end) as 'R-ME1',
(case when B.ME2 = 'TRUE' then 1 else 0 end) as 'R-ME2',
(case when B.NE1 = 'TRUE' then 1 else 0 end) as 'R-NE1',
(case when B.NE2 = 'TRUE' then 1 else 0 end) as 'R-NE2',
(case when B.NE3 = 'TRUE' then 1 else 0 end) as 'R-NE3'










from

(Select A.Num,A.RanSelection,
(Case when A.RanSelection <= @AK then 'TRUE' else 'FALSE' end) as AK,
(Case when A.RanSelection <= @AL then 'TRUE' else 'FALSE' end) as AL,
(Case when A.RanSelection <= @AR then 'TRUE' else 'FALSE' end) as AR,
(Case when A.RanSelection <= @AZ then 'TRUE' else 'FALSE' end) as AZ,
(Case when A.RanSelection <= @CA then 'TRUE' else 'FALSE' end) as CA,
(Case when A.RanSelection <= @CO then 'TRUE' else 'FALSE' end) as CO,
(Case when A.RanSelection <= @CT then 'TRUE' else 'FALSE' end) as CT,
(Case when A.RanSelection <= @DE then 'TRUE' else 'FALSE' end) as DE,
(Case when A.RanSelection <= @FL then 'TRUE' else 'FALSE' end) as FL,
(Case when A.RanSelection <= @GA then 'TRUE' else 'FALSE' end) as GA,
(Case when A.RanSelection <= @HI then 'TRUE' else 'FALSE' end) as HI,
(Case when A.RanSelection <= @IA then 'TRUE' else 'FALSE' end) as IA,
(Case when A.RanSelection <= @ID then 'TRUE' else 'FALSE' end) as ID,
(Case when A.RanSelection <= @IL then 'TRUE' else 'FALSE' end) as IL,
(Case when A.RanSelection <= @IN then 'TRUE' else 'FALSE' end) as "IN",
(Case when A.RanSelection <= @KS then 'TRUE' else 'FALSE' end) as KS,
(Case when A.RanSelection <= @KY then 'TRUE' else 'FALSE' end) as KY,
(Case when A.RanSelection <= @LA then 'TRUE' else 'FALSE' end) as LA,
(Case when A.RanSelection <= @MA then 'TRUE' else 'FALSE' end) as MA,
(Case when A.RanSelection <= @MD then 'TRUE' else 'FALSE' end) as MD,
(Case when A.RanSelection <= @ME then 'TRUE' else 'FALSE' end) as ME,
(Case when A.RanSelection <= @MI then 'TRUE' else 'FALSE' end) as MI,
(Case when A.RanSelection <= @MN then 'TRUE' else 'FALSE' end) as MN,
(Case when A.RanSelection <= @MO then 'TRUE' else 'FALSE' end) as MO,
(Case when A.RanSelection <= @MS then 'TRUE' else 'FALSE' end) as MS,
(Case when A.RanSelection <= @MT then 'TRUE' else 'FALSE' end) as MT,
(Case when A.RanSelection <= @NC then 'TRUE' else 'FALSE' end) as NC,
(Case when A.RanSelection <= @ND then 'TRUE' else 'FALSE' end) as ND,
(Case when A.RanSelection <= @NE then 'TRUE' else 'FALSE' end) as NE,
(Case when A.RanSelection <= @NH then 'TRUE' else 'FALSE' end) as NH,
(Case when A.RanSelection <= @NJ then 'TRUE' else 'FALSE' end) as NJ,
(Case when A.RanSelection <= @NM then 'TRUE' else 'FALSE' end) as NM,
(Case when A.RanSelection <= @NV then 'TRUE' else 'FALSE' end) as NV,
(Case when A.RanSelection <= @NY then 'TRUE' else 'FALSE' end) as NY,
(Case when A.RanSelection <= @OH then 'TRUE' else 'FALSE' end) as OH,
(Case when A.RanSelection <= @OK then 'TRUE' else 'FALSE' end) as OK,
(Case when A.RanSelection <= @OR then 'TRUE' else 'FALSE' end) as "OR",
(Case when A.RanSelection <= @PA then 'TRUE' else 'FALSE' end) as PA,
(Case when A.RanSelection <= @RI then 'TRUE' else 'FALSE' end) as RI,
(Case when A.RanSelection <= @SC then 'TRUE' else 'FALSE' end) as SC,
(Case when A.RanSelection <= @SD then 'TRUE' else 'FALSE' end) as SD,
(Case when A.RanSelection <= @TN then 'TRUE' else 'FALSE' end) as TN,
(Case when A.RanSelection <= @TX then 'TRUE' else 'FALSE' end) as TX,
(Case when A.RanSelection <= @UT then 'TRUE' else 'FALSE' end) as UT,
(Case when A.RanSelection <= @VA then 'TRUE' else 'FALSE' end) as VA,
(Case when A.RanSelection <= @VT then 'TRUE' else 'FALSE' end) as VT,
(Case when A.RanSelection <= @WA then 'TRUE' else 'FALSE' end) as WA,
(Case when A.RanSelection <= @WI then 'TRUE' else 'FALSE' end) as WI,
(Case when A.RanSelection <= @WV then 'TRUE' else 'FALSE' end) as WV,
(Case when A.RanSelection <= @WY then 'TRUE' else 'FALSE' end) as WY,
(Case when A.RanSelection <= @DC then 'TRUE' else 'FALSE' end) as DC,


(Case when A.RanSelection <= @ME1 then 'TRUE' else 'FALSE' end) as ME1,
(Case when A.RanSelection <= @ME2 then 'TRUE' else 'FALSE' end) as ME2,
(Case when A.RanSelection <= @NE1 then 'TRUE' else 'FALSE' end) as NE1,
(Case when A.RanSelection <= @NE2 then 'TRUE' else 'FALSE' end) as NE2,
(Case when A.RanSelection <= @NE3 then 'TRUE' else 'FALSE' end) as NE3




from

(SELECT TOP 1000000 [Num],RAND(convert(varbinary, newid())) as RanSelection
  FROM [Database].[dbo].[Numbers])A)B)C)D

  group by D.WinLose)E




  )F

```
$\endgroup$
0
$\begingroup$

I actually inputted Nate Silver’s 2016 state-by-state odds into this and the results came very close to his overall 2016 prediction seen here.

https://projects.fivethirtyeight.com/2016-election-forecast/

| cite | improve this answer | |
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.