CODEWITHSUNDEEP
sqlsqlite
James
James

Reputation: 4052

Tricky SQL Query Help Needed

I have recently written some code that logs data from the betting exchange website. The code creates a simple, one table SQLite3 database. This database stores odds for games as the games progress in-play.

The .schema looks like this:

CREATE TABLE in_play (
    status text,
    date text,
    marketID int,
    marketName text,
    total_matched real,
    home_back real,
    home_lay real,
    away_back real,
    away_lay real,
    draw_back real,
    draw_lay real
);

In the table status can either be "ACTIVE" or "SUSPENDED", I only want to keep "ACTIVE" markets. I'm also interested in looking at games that are drawing initially, and then end in a draw, ie. draw_back has lower value than away_back and home_back initially, and then the final draw_back price is lower than the first draw_back price.

To Illustrate this better here's a log of one particular game that the query should find:

ACTIVE|2013-06-27 13:13:17.577877|109868503|Home Utd v Tanjong Pagar Utd|49905.54|6.2|6.4|9.6|11.0|1.33|1.34
ACTIVE|2013-06-27 13:14:18.413846|109868503|Home Utd v Tanjong Pagar Utd|49905.54|6.0|6.6|11.5|13.0|1.31|1.32
ACTIVE|2013-06-27 13:15:19.320099|109868503|Home Utd v Tanjong Pagar Utd|65377.29|7.0|7.4|14.0|16.5|1.22|1.25
ACTIVE|2013-06-27 13:16:20.180560|109868503|Home Utd v Tanjong Pagar Utd|65377.29|9.6|10.0|14.5|17.5|1.2|1.22
ACTIVE|2013-06-27 13:17:21.093332|109868503|Home Utd v Tanjong Pagar Utd|65377.29|10.5|30.0|15.5|40.0|1.15|1.17
ACTIVE|2013-06-27 13:18:21.912865|109868503|Home Utd v Tanjong Pagar Utd|65377.29|13.5|19.5|16.5|26.0|1.11|1.12

I would like a query that selects all games like this and checks that they most likely finished in a draw. Ideally it will also add up winnings/losses from betting on games like this by counting up the number that finish as a draw against the number that don't finish as a draw.

So far the query I have is

select *
from in_play
where status="ACTIVE"
    and draw_back < away_back
    and draw_back < home_back;

but I don't have much SQL experience.

Sorry for the length of this post, I hope this makes sense.

Upvotes: 0

Views: 75

Answers (1)

user1676075
user1676075

Reputation: 3086

I believe this should do it (although it will be slow and inefficient; I'd probably solve it mostly in code by looking for the initial or final case, then working from there):

select * from in_play a, in_play b where a.status='ACTIVE'
and a.draw_back < a.away_back and a.draw_back < a.home_back
and b.marketID = a.marketID
and b.draw_back < a.draw_back
and a.date = (select min(c.date) from in_play c where c.marketID = a.marketID)
and b.date = (select max(d.date) from in_play d where d.marketID = a.marketID)

Upvotes: 1

Related Questions