MSSQL schema for betting simulation based on data.betfair.com

betting mssql schema

-- Create betfair data table.
CREATE TABLE [dbo].[betfair_data](
	[SPORTS_ID] [varchar](50) NOT NULL,
	[EVENT_ID] [int] NOT NULL,
	[SETTLED_DATE] [datetime] NULL,
	[FULL_DESCRIPTION] [varchar](255) NOT NULL,
	[SCHEDULED_OFF] [datetime] NOT NULL,
	[EVENT] [varchar](50) NOT NULL,
	[DT_ACTUAL_OFF] [datetime] NULL,
	[SELECTION_ID] [int] NOT NULL,
	[SELECTION] [varchar](50) NOT NULL,
	[ODDS] [decimal](18, 2) NOT NULL,
	[NUMBER_BETS] [int] NOT NULL,
	[VOLUME_MATCHED] [decimal](18, 2) NOT NULL,
	[LATEST_TAKEN] [datetime] NOT NULL,
	[FIRST_TAKEN] [datetime] NOT NULL,
	[WIN_FLAG] [bit] NULL,
	[IN_PLAY] [varchar](50) NOT NULL
) ON [PRIMARY]

-- Create market probability table.
CREATE TABLE [dbo].[market_prob](
	[market_id] [int] NOT NULL,
	[full_description] [varchar](255) NULL,
	[scheduled_off] [datetime] NULL,
	[selection_id] [int] NOT NULL,
	[selection] [varchar](50) NULL,
	[probability] [decimal](18, 4) NOT NULL,
	[surface] [varchar](50) NULL,
	[match_type] [varchar](50) NULL
) ON [PRIMARY]

-- Create betfair data with probabilities view.
CREATE VIEW [dbo].[sim_betfair_data_with_prob]
AS
SELECT     p.probability AS predicted_prob, s.SPORTS_ID, s.EVENT_ID, s.SETTLED_DATE, s.FULL_DESCRIPTION, s.SCHEDULED_OFF, s.EVENT, s.DT_ACTUAL_OFF, 
                      s.SELECTION_ID, s.SELECTION, s.ODDS, s.NUMBER_BETS, s.VOLUME_MATCHED, s.LATEST_TAKEN, s.FIRST_TAKEN, s.WIN_FLAG, s.IN_PLAY
FROM         dbo.betfair_data AS s LEFT OUTER JOIN
                      dbo.market_prob AS p ON s.EVENT_ID = p.market_id AND s.SELECTION_ID = p.selection_id

-- Create view for markets
CREATE VIEW [dbo].[markets]
AS
SELECT     SPORTS_ID, EVENT_ID, FULL_DESCRIPTION, EVENT, SCHEDULED_OFF, SELECTION_ID, SELECTION
FROM         dbo.betfair_data
GROUP BY SPORTS_ID, EVENT_ID, FULL_DESCRIPTION, EVENT, SCHEDULED_OFF, SELECTION_ID, SELECTION

-- Create bets view
CREATE VIEW [dbo].[bets]
AS
SELECT     1 * (ODDS * WIN_FLAG - 1) AS total, SPORTS_ID, EVENT_ID, SETTLED_DATE, FULL_DESCRIPTION, SCHEDULED_OFF, EVENT, DT_ACTUAL_OFF, SELECTION_ID, 
                      SELECTION, ODDS, NUMBER_BETS, VOLUME_MATCHED, LATEST_TAKEN, FIRST_TAKEN, WIN_FLAG, IN_PLAY, predicted_prob
FROM         dbo.sim_betfair_data_with_prob
WHERE     (CAST(VOLUME_MATCHED AS real) > 1000) AND (ODDS * predicted_prob > 1)

-- Create p&l including 5% commission view.
create view [dbo].[profit_loss] as 
SELECT     win_total + loss_total AS profit_loss
FROM         
                       (SELECT     SUM(total) AS win_total
                       FROM         (SELECT     EVENT_ID, SUM(total) AS total
                                              FROM          dbo.bets AS settled_bets
                                              GROUP BY EVENT_ID
                                              HAVING      SUM(total) < 0) LOSS) LOSS_SUMM, 
(SELECT     SUM(total * 0.95) AS loss_total
                       FROM          (SELECT     EVENT_ID, SUM(total) AS total
                                               FROM          dbo.bets AS settled_bets
                                               GROUP BY EVENT_ID
                                               HAVING      SUM(total) > 0) WON) AS WON_SUMM