How to know the stack at the end of a blind level?

[maxtamines]

I have never used postgreSQL and i'm quite lost even after reading the doc and the FAQ.
Is it possible to do that?

Max.

[kraada]

I'm not sure there's any easy way to do this at this point because there's no information in the database about when the blinds go up. The blinds on a given hand are recorded, but I'm not sure off the top of my head if there's any easy way to run a report comparing hands in a sequential fashion. "If hand 1 has blinds X and hand 2 has blinds bigger than X, tell me the stack at the end of hand 1" would be the only way I can imagine this query, but I don't think there's a way to build the antecedent at this time.

[maxtamines]

Do you plan to allow the query of this information?
I think that it can be very useful for SNG player.

[kraada]

If you're looking at your own stats and just want to see how you do during a blind level you can always use the Filters to filter for a given blind level (say 15/30) and see how you do in that level in general.

[maxtamines]

It's not what I want to do. I want to compute some complexe statistics about SNG and it seems that the only difficulty is the obtention of the stack at the end of a blind level.

[maxtamines]

I have an idea.
Is it possible to create a stat NbHandsOfLevel(x) which count how many hands have been played in a particular SNG in the level(x) (level(1)=10/20, level(2)=15/30, level(3)=25/50 etc..)?
Then it must be possible to create a stat NbHandsPlayedAfterLevel(x)=sum(NbHandsOfLevel(1:x))
And then to store our stack at the start of the hand sum(NbHandsPlayedAfterLevel(x))?

[kraada]

I'm not sure you could do that for each tournament without doing it for all tournaments.

That is, you can say "how many lifetime hands have I played at 10/20 blind levels" but that won't help you know for this tournament where the blind levels shifted.

[maxtamines]

Hum indeed.
Another idea that I have is to compute the average stack that i have in a SNG after X hand.
And then I will assume that the first level of blind is finish after, for example, 10hands in my SNG and then consider that my average stack after level 1 is my average stack after 10hands.
Is it possible to know the stack after the Xth hand? And then to compute some average (average stack after Xth hand) or distribution (%of ITM, 1st, 2nd and 3rd place if my stack after th Xth hands is between 1000 and 1200) on it?

[kraada]

As far as I'm aware there's know way to know "this is the 17th hand of the SnG".

However, I think you can access the tournament start time statistics, and using the PostgreSQL time functions you can see how much time has elapsed since the beginning of the tournament (tournament start time - hand time), so you might be able to actually make some of this work.

The discussion in this thread goes into how to use the PostgreSQL time functions.

[maxtamines]

I've tried to use the time functions for obtaining the stat that i want it seems to work but i have another problem when i complexify the stat.
I want to compute the estimate value of my stack after the level1, therefore I have created some colums in player statistics:

One which count the number of hands played between the 10th and the 11th minutes of my SNG (in the majority of the case it is the first hand played in the 2nd level, in some case there is also the second hand...) :
sum(if[(tourney_holdem_hand_summary.date_played >( tourney_holdem_summary.date_start + interval'10 MINUTES')) AND (tourney_holdem_hand_summary.date_played <(tourney_holdem_summary.date_start + interval'11 MINUTES')) AND (tourney_holdem_hand_player_detail.amt_before>1499) AND(tourney_holdem_hand_player_detail.amt_before<1550),1,0])

One which count the number of hands played between the 10th and the 11th minutes of my SNG AND where i finish 1st in the tournament:
sum(if[(tourney_holdem_hand_summary.date_played >( tourney_holdem_summary.date_start + interval'10 MINUTES')) AND (tourney_holdem_hand_summary.date_played <(tourney_holdem_summary.date_start + interval'11 MINUTES')) AND (tourney_holdem_hand_player_detail.amt_before>1499) AND(tourney_holdem_hand_player_detail.amt_before<1550) AND(tourney_holdem_results.val_finish=1),1,0])

One which count the number of hands played between the 10th and the 11th minutes of my SNG AND where i finish 2nd in the tournament:
sum(if[(tourney_holdem_hand_summary.date_played >( tourney_holdem_summary.date_start + interval'10 MINUTES')) AND (tourney_holdem_hand_summary.date_played <(tourney_holdem_summary.date_start + interval'11 MINUTES')) AND (tourney_holdem_hand_player_detail.amt_before>1499) AND(tourney_holdem_hand_player_detail.amt_before<1550) AND(tourney_holdem_results.val_finish=2),1,0])

One which count the number of hands played between the 10th and the 11th minutes of my SNG AND where i finish 3rd in the tournament:
sum(if[(tourney_holdem_hand_summary.date_played >( tourney_holdem_summary.date_start + interval'10 MINUTES')) AND (tourney_holdem_hand_summary.date_played <(tourney_holdem_summary.date_start + interval'11 MINUTES')) AND (tourney_holdem_hand_player_detail.amt_before>1499) AND(tourney_holdem_hand_player_detail.amt_before<1550) AND(tourney_holdem_results.val_finish=3),1,0])

For each of these colums I have created a stat and i have creating a stat that gives me the ev of my stack :
(stat_sng_Pl1_1500_1550_level1 + stat_sng_Pl2_1500_1550_level1 + stat_sng_Pl3_1500_1550_level1) / stat_sng_Hands_1500_1550_level1

But that do not give accurate results. It gives me a expected value 0.33 for all the interval of stack :-(.
It seems that if a have X hands played between the 10th and the 11th minutes, I have X/3 place of 1, X/3 place of 2 and X/3 place of 3 etc.. I have tested on different sample where i'm sure that it is not the case.
So it seems that there is a problem with the use of tourney_holdem_results.val_finish in Holdem Tournament Player Statistics, is this guess true?
Do you see a way to compute what I want?

Max.