Filter for facing 3bet w exactly 1 cold call

[JoeDeertayAA]

Hello all,

I'm trying to create reports for how the population reacts to 3bets as their first action in a hand with different numbers of cold callers with any action before the 3bet (any number of limpers and any number of 2bet cold calls), and I can't seem to find a viable expression that works for more than 0 3bet cold calls.

For the 0 coldcalls report, I used the following expression filter that just covers all the position combinations between 3bettor and hero and just made everyone else between them fold to the 3bet, which seems to work perfectly as intended (along with some basic simple filters):

Code: Select all

tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.flg_p_3bet and (thps.amt_p_raise_made / (thps.amt_before - thps.amt_ante)) < 0.5)
and ((substring(tourney_hand_summary.str_aggressors_p from 3 for 1)::int = 9
  and tourney_hand_player_statistics.position = 8)

or (substring(tourney_hand_summary.str_aggressors_p from 3 for 1)::int = 0
  and (tourney_hand_player_statistics.position = 9
  or (tourney_hand_player_statistics.position = 8
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 9 and thps.enum_p_3bet_action = 'F'))))

or (substring(tourney_hand_summary.str_aggressors_p from 3 for 1)::int = 1
  and (tourney_hand_player_statistics.position = 0
  or (tourney_hand_player_statistics.position = 9
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 0 and thps.enum_p_3bet_action = 'F'))
  or (tourney_hand_player_statistics.position = 8
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 0 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 9 and thps.enum_p_3bet_action = 'F'))))

or (substring(tourney_hand_summary.str_aggressors_p from 3 for 1)::int = 2
  and (tourney_hand_player_statistics.position = 1
  or (tourney_hand_player_statistics.position = 0
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 1 and thps.enum_p_3bet_action = 'F'))
  or (tourney_hand_player_statistics.position = 9
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 1 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 0 and thps.enum_p_3bet_action = 'F'))
  or (tourney_hand_player_statistics.position = 8
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 1 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 0 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 9 and thps.enum_p_3bet_action = 'F'))))

or (substring(tourney_hand_summary.str_aggressors_p from 3 for 1)::int = 3
  and (tourney_hand_player_statistics.position = 2
  or (tourney_hand_player_statistics.position = 1
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.enum_p_3bet_action = 'F'))
  or (tourney_hand_player_statistics.position = 0
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 1 and thps.enum_p_3bet_action = 'F'))
  or (tourney_hand_player_statistics.position = 9
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 1 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 0 and thps.enum_p_3bet_action = 'F'))
  or (tourney_hand_player_statistics.position = 8
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 1 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 0 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 9 and thps.enum_p_3bet_action = 'F'))))

or (substring(tourney_hand_summary.str_aggressors_p from 3 for 1)::int = 4
  and (tourney_hand_player_statistics.position = 3
  or (tourney_hand_player_statistics.position = 2
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.enum_p_3bet_action = 'F'))
  or (tourney_hand_player_statistics.position = 1
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.enum_p_3bet_action = 'F'))
  or (tourney_hand_player_statistics.position = 0
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 1 and thps.enum_p_3bet_action = 'F'))
  or (tourney_hand_player_statistics.position = 9
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 1 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 0 and thps.enum_p_3bet_action = 'F'))
  or (tourney_hand_player_statistics.position = 8
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 1 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 0 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 9 and thps.enum_p_3bet_action = 'F'))))

or (substring(tourney_hand_summary.str_aggressors_p from 3 for 1)::int = 5
  and (tourney_hand_player_statistics.position = 4
  or (tourney_hand_player_statistics.position = 3
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 4 and thps.enum_p_3bet_action = 'F'))
  or (tourney_hand_player_statistics.position = 2
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 4 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.enum_p_3bet_action = 'F'))
  or (tourney_hand_player_statistics.position = 1
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 4 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.enum_p_3bet_action = 'F'))
  or (tourney_hand_player_statistics.position = 0
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 4 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 1 and thps.enum_p_3bet_action = 'F'))
  or (tourney_hand_player_statistics.position = 9
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 4 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 1 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 0 and thps.enum_p_3bet_action = 'F'))
  or (tourney_hand_player_statistics.position = 8
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 4 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 1 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 0 and thps.enum_p_3bet_action = 'F')
    and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 9 and thps.enum_p_3bet_action = 'F')))))

However, this code is already very long and is only the minimum number of cases possible for people to react to 3bets before hero, and each additional cold caller makes the code that much longer covering every possible case this way. A similar method for just 1 cold caller brings the code over the character limit.

When I realized that, I tried to shorten the code by doing it backwards from position of the 1 cold caller and filtering for all possible 3bettor and hero position combinations while having every other position fold to the 3bets:

Code: Select all

(tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 4 and thps.enum_p_3bet_action = 'C')
  and ((substring(tourney_hand_summary.str_aggressors_p from 3 for 1)::int = 5 and tourney_hand_player_statistics.position = (8|9|0|1|2|3))
  and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position <> tourney_hand_player_statistics.position and not(thps.flg_p_limp or thps.flg_p_first_raise or thps.flg_p_3bet_opp) and thps.enum_p_3bet_action = 'F')))

or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.enum_p_3bet_action = 'C')
  and ((substring(tourney_hand_summary.str_aggressors_p from 3 for 1)::int = (4|5) and tourney_hand_player_statistics.position = (8|9|0|1|2))
  and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position <> tourney_hand_player_statistics.position and not(thps.flg_p_limp or thps.flg_p_first_raise or thps.flg_p_3bet_opp) and thps.enum_p_3bet_action = 'F')))

or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.enum_p_3bet_action = 'C')
  and ((substring(tourney_hand_summary.str_aggressors_p from 3 for 1)::int = (3-5) and tourney_hand_player_statistics.position = (8|9|0|1))
  and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position <> tourney_hand_player_statistics.position and not(thps.flg_p_limp or thps.flg_p_first_raise or thps.flg_p_3bet_opp) and thps.enum_p_3bet_action = 'F')))

or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 1 and thps.enum_p_3bet_action = 'C')
  and ((substring(tourney_hand_summary.str_aggressors_p from 3 for 1)::int = (2-5) and tourney_hand_player_statistics.position = (8|9|0))
  and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position <> tourney_hand_player_statistics.position and not(thps.flg_p_limp or thps.flg_p_first_raise or thps.flg_p_3bet_opp) and thps.enum_p_3bet_action = 'F')))

or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 0 and thps.enum_p_3bet_action = 'C')
  and ((substring(tourney_hand_summary.str_aggressors_p from 3 for 1)::int = (1-5) and tourney_hand_player_statistics.position = (8|9))
  and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position <> tourney_hand_player_statistics.position and not(thps.flg_p_limp or thps.flg_p_first_raise or thps.flg_p_3bet_opp) and thps.enum_p_3bet_action = 'F')))

or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 9 and thps.enum_p_3bet_action = 'C')
  and ((substring(tourney_hand_summary.str_aggressors_p from 3 for 1)::int = (0-5) and tourney_hand_player_statistics.position = 8)
  and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position <> tourney_hand_player_statistics.position and not(thps.flg_p_limp or thps.flg_p_first_raise or thps.flg_p_3bet_opp) and thps.enum_p_3bet_action = 'F')))


This validates, but after a long time loading the report it ended up with 0 hands, which definitely seems wrong, so I must have messed up again somewhere. I'm learning this as I go and I've tried everything I can think of or find and can't get it to work, so can anyone please help me out if it's even possible? Thank you so much!

P.S. - I know the "::int"s might not need to be in there, but I guess I forgot to trim it after copy pasting. My best guess is the "thps.position <> tourney_hand_player_statistics.position" might be causing some kind of problem, but I really have no idea.

[JoeDeertayAA]

Oh and fwiw, and filtering for squeeze opportunities didn't seem to work either facing 3bets, since the NOT() filter was giving me hands with cold calls and vice versa.

[JoeDeertayAA]

Eureka!

After my session I tried a couple more ideas and it seems to finally be working perfectly.

Here's the expression filter for first action facing a 3bet with exactly 1 cold caller, ANY number of limpers, and ANY number of 2bet cold calls:

Code: Select all

(substring(tourney_hand_summary.str_aggressors_p from 3 for 1)::int = 0
    and (tourney_hand_player_statistics.position = 8
      and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 9 and thps.enum_p_3bet_action = 'C')))

  or (substring(tourney_hand_summary.str_aggressors_p from 3 for 1)::int = 1
    and ((tourney_hand_player_statistics.position = 9
      and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 0 and thps.enum_p_3bet_action = 'C'))
    or (tourney_hand_player_statistics.position = 8
      and ((tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 0 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 9 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 0 and thps.enum_p_3bet_action = 'F')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 9 and thps.enum_p_3bet_action = 'C'))))))

  or (substring(tourney_hand_summary.str_aggressors_p from 3 for 1)::int = 2
    and (tourney_hand_player_statistics.position = 0
      and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 1 and thps.enum_p_3bet_action = 'C'))
    or (tourney_hand_player_statistics.position = 9
      and ((tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 1 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 0 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 1 and thps.enum_p_3bet_action = 'F')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 0 and thps.enum_p_3bet_action = 'C'))))
    or (tourney_hand_player_statistics.position = 8
      and ((tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 1 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 0 and thps.position = 9 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 0 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 1 and thps.position = 9 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 9 and thps.enum_p_3bet_action = 'F')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 1 and thps.position = 0 and thps.enum_p_3bet_action = 'F')))))

  or (substring(tourney_hand_summary.str_aggressors_p from 3 for 1)::int = 3
    and ((tourney_hand_player_statistics.position = 1
      and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.enum_p_3bet_action = 'C'))
    or (tourney_hand_player_statistics.position = 0
      and ((tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 1 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.enum_p_3bet_action = 'F')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 1 and thps.enum_p_3bet_action = 'C'))))
    or (tourney_hand_player_statistics.position = 9
      and ((tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 1 and thps.position = 0 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 1 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.position = 0 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 0 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.position = 1 and thps.enum_p_3bet_action = 'F'))))
    or (tourney_hand_player_statistics.position = 8
      and ((tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 1 and thps.position = 0 and thps.position = 9 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 1 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.position = 0 and thps.position = 9 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 0 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.position = 1 and thps.position = 9 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 9 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.position = 1 and thps.position = 0 and thps.enum_p_3bet_action = 'F'))))))

  or (substring(tourney_hand_summary.str_aggressors_p from 3 for 1)::int = 4
    and ((tourney_hand_player_statistics.position = 2
      and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.enum_p_3bet_action = 'C'))
    or (tourney_hand_player_statistics.position = 1
      and ((tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.enum_p_3bet_action = 'F'))))
    or (tourney_hand_player_statistics.position = 0
      and ((tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.position = 1 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.position = 1 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 1 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.position = 2 and thps.enum_p_3bet_action = 'F'))))
    or (tourney_hand_player_statistics.position = 9
      and ((tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.position = 1 and thps.position = 0 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.position = 1 and thps.position = 0 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 1 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.position = 2 and thps.position = 0 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 0 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.position = 2 and thps.position = 1 and thps.enum_p_3bet_action = 'F'))))
    or (tourney_hand_player_statistics.position = 8
      and ((tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.position = 1 and thps.position = 0 and thps.position = 9 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.position = 1 and thps.position = 0 and thps.position = 9 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 1 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.position = 2 and thps.position = 0 and thps.position = 9 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 0 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.position = 2 and thps.position = 1 and thps.position = 9 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 9 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.position = 2 and thps.position = 1 and thps.position = 0 and thps.enum_p_3bet_action = 'F'))))))

  or (substring(tourney_hand_summary.str_aggressors_p from 3 for 1)::int = 5
    and ((tourney_hand_player_statistics.position = 3
      and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 4 and thps.enum_p_3bet_action = 'C'))
    or (tourney_hand_player_statistics.position = 2
      and ((tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 4 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 4 and thps.enum_p_3bet_action = 'F')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.enum_p_3bet_action = 'C'))))
    or (tourney_hand_player_statistics.position = 1
      and ((tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 4 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.position = 2 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 4 and thps.position = 2 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 4 and thps.position = 3 and thps.enum_p_3bet_action = 'F'))))
    or (tourney_hand_player_statistics.position = 0
      and ((tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 4 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.position = 2 and thps.position = 1 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 4 and thps.position = 2 and thps.position = 1 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 4 and thps.position = 3 and thps.position = 1 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 1 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 4 and thps.position = 3 and thps.position = 2 and thps.enum_p_3bet_action = 'F'))))
    or (tourney_hand_player_statistics.position = 9
      and ((tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 4 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.position = 2 and thps.position = 1 and thps.position = 0 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 4 and thps.position = 2 and thps.position = 1 and thps.position = 0 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 4 and thps.position = 3 and thps.position = 1 and thps.position = 0 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 1 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 4 and thps.position = 3 and thps.position = 2 and thps.position = 0 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 0 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 4 and thps.position = 3 and thps.position = 2 and thps.position = 1 and thps.enum_p_3bet_action = 'F'))))
    or (tourney_hand_player_statistics.position = 8
      and ((tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 4 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.position = 2 and thps.position = 1 and thps.position = 0 and thps.position = 9 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 3 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 4 and thps.position = 2 and thps.position = 1 and thps.position = 0 and thps.position = 9 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 2 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 4 and thps.position = 3 and thps.position = 1 and thps.position = 0 and thps.position = 9 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 1 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 4 and thps.position = 3 and thps.position = 2 and thps.position = 0 and thps.position = 9 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 0 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 4 and thps.position = 3 and thps.position = 2 and thps.position = 1 and thps.position = 9 and thps.enum_p_3bet_action = 'F'))
        or (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 9 and thps.enum_p_3bet_action = 'C')
          and tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 4 and thps.position = 3 and thps.position = 2 and thps.position = 1 and thps.position = 0 and thps.enum_p_3bet_action = 'F'))))))

It also seems like you could make some minor adjustments if you wanted 2-6 cold callers and it should still fit the character limit and validate, and working code for 0 cold callers is in the OP. I haven't tried it, but it would only require some copy/pasting positions between call and fold subqueries.

[JoeDeertayAA]

So it looks like I found a bug while testing for 3 people cold calling a 3bet, but I can't figure out what's wrong.

This is the segment of code for the filter I have for facing a 3bet from HJ/position 2 with 3 cold callers in BB:

Code: Select all

tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.flg_p_3bet and (thps.amt_p_raise_made / (thps.amt_before - thps.amt_ante)) <= 0.20)
and (substring(tourney_hand_summary.str_aggressors_p from 3 for 1)::int = 2
    and (tourney_hand_player_statistics.position = 8
      and (tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 1 and thps.position = 0 and thps.position = 9 and thps.enum_p_3bet_action = 'C'))))

However, this expression is for some reason not capturing the following hand:
PokerStars - 40/80 NL - Holdem - 8 players
Hand converted by PokerTracker 4

MP+1: 129.55 BB
Hero (CO): 116.54 BB
BTN: 43.34 BB
SB: 137.85 BB
BB: 198.72 BB
UTG: 125 BB
UTG+1: 122.94 BB
MP: 126.06 BB

SB posts SB 0.5 BB, BB posts BB 1 BB

Pre Flop: (pot: 1.5 BB) Hero has 3 A

fold, fold, MP raises to 2 BB, MP+1 raises to 3 BB, Hero calls 3 BB, BTN calls 3 BB, SB calls 2.5 BB, fold, MP calls 1 BB

Flop: (16 BB, 5 players) 7 Q 2
SB bets 5.12 BB, fold, fold, fold, fold

SB wins 16 BB


Since positions 1, 0, and 9 all cold called the 3bet before the BB, the hand should show up in the report, but it doesn't. Can anybody please help me figure out what's going on here? There are some other position combos with similar filters that aren't working either, but I imagine the solution is the same. Thank you!

[Flag_Hippo]

Unfortunately I cannot imported converted forum format hands into PokerTracker 4 to test with your code so please post the original hand history or if you prefer send it privately in a Support Ticket with a link to your post.

[Flag_Hippo]

After looking again it appears this is your issue:

Code: Select all

tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 1 and thps.position = 0 and thps.position = 9 and thps.enum_p_3bet_action = 'C')

The subquery is testing for each individual player and it's not possible for a single player to be in all 3 of these positions so you would need to test for each separately e.g:

Code: Select all

tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 1 and thps.enum_p_3bet_action = 'C') AND tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 9 and thps.enum_p_3bet_action = 'C') AND tourney_hand_player_statistics.id_hand in (select thps.id_hand from tourney_hand_player_statistics thps where thps.position = 0 and thps.enum_p_3bet_action = 'C')

[JoeDeertayAA]

Oh ok. Guess I misunderstood how the subquery worked. I thought it would find the id_hand and filter for cases where the players in positions x, y, and z all coldcalled, but didn't know they all had to be the same person in that syntax. I tired it the "right" way first but the code went over the character limit, so maybe I just have to break it up into multiple reports. Thanks for confirming!

[BillGatesIII]

I am not sure if I understand you correctly but for a report with all hands with a preflop 3bet and one cold caller this piece of code should work.

Code: Select all

id_hand in (select id_hand from tourney_hand_summary
where length(str_aggressors_p) = 3
and right(str_actors_p, 1) <> right(str_aggressors_p, 1)
and length(substr(str_actors_p, length(str_actors_p) - strpos(reverse(str_actors_p), right(str_aggressors_p, 1)) + 2)) = 1
and strpos(left(str_actors_p, length(str_actors_p) -1), right(str_actors_p, 1)) = 0)