custom 3b stat vs Hero

[krychu-lodz]

Hi,
I don't have much experience in creating custom stats nor in any sort of coding but I tried creating a custom 3b stat for my HUD based on other custom stats and info found on this forum. I have a basic understanding what's for what in there but I lack of knowledge so maybe someone could help me finding a mistake in what I've created as it doesn't work properly from what I've noticed.

Desrciption:
3 handed tournament, 3 players dealt into the hand, Hero on the BU, opening with a raise. I wanted to create a 3B non allin stat for the player in SB vs Hero only (2 stats to be exact for different stacksizes) and I came up with these columns:
for the actual 3b

Code: Select all

sum(if[tourney_hand_summary.cnt_players = 3
AND tourney_hand_player_statistics.position = 9
AND tourney_hand_summary.str_actors_p LIKE '09'
AND tourney_hand_summary.str_aggressors_p LIKE '809'
AND (tourney_hand_player_statistics.amt_p_effective_stack / tourney_blinds.amt_bb  BETWEEN 10 AND 24.99)
AND exists (
        select 1
        from tourney_hand_player_statistics hero
        where hero.id_hand = tourney_hand_player_statistics.id_hand
        and hero.flg_hero
        and hero.position = 0
        and hero.flg_p_first_raise
    )

AND tourney_hand_player_statistics.flg_p_3bet
AND (tourney_hand_player_statistics.amt_p_raise_made / tourney_blinds.amt_bb <= 9), 1, 0])

and for the opportunity to 3b:

Code: Select all

sum(if[tourney_hand_summary.cnt_players = 3
AND tourney_hand_player_statistics.position = 9
AND tourney_hand_summary.str_actors_p LIKE '0'
AND tourney_hand_summary.str_aggressors_p LIKE '80'
AND (tourney_hand_player_statistics.amt_p_effective_stack / tourney_blinds.amt_bb  BETWEEN 10 AND 24.99)
AND exists (
        select 1
        from tourney_hand_player_statistics hero
        where hero.id_hand = tourney_hand_player_statistics.id_hand
        and hero.flg_hero
        and hero.position = 0
        and hero.flg_p_first_raise
    )

AND tourney_hand_player_statistics.flg_p_3bet_opp , 1, 0])

If somebody could spot a mistake in there I would be very appreciated

[Flag_Hippo]

AND tourney_hand_summary.str_actors_p LIKE '09'
AND tourney_hand_summary.str_aggressors_p LIKE '809'

This isn't going to count any hands where further chips are invested after the small blind 3bet e.g. a BTN call or 4bet. You should instead use:

Code: Select all

AND tourney_hand_summary.str_actors_p LIKE '09%'
AND tourney_hand_summary.str_aggressors_p LIKE '809%'

For more information on pattern matching in PostgreSQL see this guide.

AND tourney_hand_summary.str_actors_p LIKE '0'
AND tourney_hand_summary.str_aggressors_p LIKE '80'

Same things applies here - this isnt going to count any hands where a 3Bet occurs as the string tests end on the BTN raise.

[krychu-lodz]

Thanks!

I thought it might have something to do with the "%" symbol that I saw in some other codes but couldn't find any info how to use it in stats coding.
So it basically means like "anything that happens after"? ;)
And all I need to do is to add this symbol in exactly 4 spots and the rest is alright?

[Flag_Hippo]

So it basically means like "anything that happens after"? ;)

Correct.

And all I need to do is to add this symbol in exactly 4 spots and the rest is alright?

Yes - everything else looks good.

[krychu-lodz]

Thank you very much!