SNG Avg Time SQL

[bengele]

I have always wondered how long it takes me to finish/bust out of a tourney so I finally broke down and wrote a SQL query to figure it out and thought I would share.

First you need to find your player ids by running this query

select * from players where screen_name = 'username'

Then you can execute this query

select sum(time) / count(*) as avg_time, count(*) as count from
(select ga.tourney_id, min(ga.date_played), max(ga.date_played), age(max(ga.date_played), min(ga.date_played)) as time from
tourney_game_players as pl join tourney_game as ga on pl.tourney_id = ga.tourney_id and pl.game_id = ga.game_id
join tourney as trny on ga.tourney_id = trny.tourney_id
where pl.player_id in (ids) and trny.buy_in = amount and table_type = 1 group by ga.tourney_id) as qry

by putting your ids in the id field (put them in as a comma separated list) and set the buy in level. If you want to run it for all buy in levels either remove the buy in condition or change it to trny.buy_in > 0

I think this will only work for the postgres database because Access doesn't have the age function. I could be wrong however. You can execute the query by going to Start -> Programs -> PostgresSQL 8.0 -> pgAdmin III and then select the database and choose Tools -> Connect enter your password and the choose Tools -> Query Tool. After you have entered the query use Query -> Execute to run it.

I have lasted about 36 minutes over ~400 tourneys. I would be interested as to what other are averaging.

Also if any one finds a bug in my qurey (you know like a decimal point out of position) could you let me know.

36 Mins from SNG tracker

[MegaBet]

42 minutes from SNG Tracker.

37 Mins - SNG Tracker.

[AliasMrJones]

Here's what I did in Access...

I created an Access database and linked in relevant PT tables. Then I created a query called TourneyLength like so:

SELECT tourney.tourney_id, Min(tourney_game.date_played) AS MinOfdate_played, Max(tourney_game.date_played) AS MaxOfdate_played, (Max([tourney_game].[date_played])-Min([tourney_game].[date_played]))*1440 AS Tourney_Length
FROM tourney INNER JOIN tourney_game ON tourney.tourney_id = tourney_game.tourney_id
WHERE (((tourney.tourney_type)=1) AND ((tourney.table_type)=1) AND ((tourney.buy_in)=50) AND ((tourney.fee)=5))
GROUP BY tourney.tourney_id;

This computes tourney length by tourney for $55 single table SnG. Replace the 50 and 5 with amounts for the buy-in you're looking for.

Then I wrote a query that uses the results of the query above to give min, max and avg times called TourneyLengthSummary:

SELECT Min(TourneyLength.Tourney_Length) AS MinOfTourney_Length, Max(TourneyLength.Tourney_Length) AS MaxOfTourney_Length, Avg(TourneyLength.Tourney_Length) AS AvgOfTourney_Length
FROM TourneyLength;