Creating a report with players >x amount of hands on

[monkaSSSS]

Hi,

Im wondering if I can create a hand report for players that I have a certain number of hands on. For example, can I create a report with players that have >1k hands in my database?

Thanks

[Flag_Hippo]

Do you want to see the hands from your perspective or those of villains? Are these heads up hands and/or ring game hands? For heads up hands you can use the following expression which filters to hands where players with a specific sample size were at the table against you:

Code: Select all

cash_hand_player_statistics.id_hand in (select chps.id_hand from cash_hand_player_statistics chps, cash_cache cc where chps.id_player = cc.id_player group by chps.id_hand, chps.id_player having ((((sum(cc.cnt_hands))) between X and Y)))

where X is the minimum sample of hands and Y is the maximum sample (which needs to be less than Heros own number of hands). Bear in mind if these are ring game hands then it will filter to hands where at least 1 player at the table has the specified sample size.

[monkaSSSS]

Hey Flag_Hippo, thanks for the response!

I was looking to get the hands from villains perspective (Villain with >x hands) against anyone on the table in hu pots in 6max games. Apologies, I should have specified in my first post!

[Flag_Hippo]

To see hands from villains perspective turn off the 'Filter On Active Player' option and add a filter for NOT(Player is Hero). You can use the expression for 6max games but it will return hands where there was at least one player with that sample size on the table so for a single 6 max hand you would see the hand in your report 5 times - one for each of the villains at the table. I am not aware of a method to isolate the hands in the report to the villain(s) with the specified sample.

[monkaSSSS]

Cool. Thanks a lot for your help!

[powi8]

Adding your filter expression to a hand report doesn't work with the Small Stakes PT4 License. Part of the expression isn't included in the query when PT4 adds the small stakes license anti-piracy filter expression.

From the log:

Executing SQL (Thread): SELECT (cash_hand_summary.id_hand) as "id_hand", (cash_hand_summary.id_site) as "id_site", (cash_hand_summary.hand_no) as "hand_no", (cash_hand_summary.id_gametype) as "id_gametype_summary", (player.player_name) as "str_player", (cash_hand_summary.flg_note) as "flg_note", (cash_hand_summary.flg_tag) as "flg_tag", (timezone('UTC', cash_hand_player_statistics.date_played + INTERVAL '0 HOURS')) as "date_played", (cash_hand_player_statistics.id_limit) as "id_limit", ((case when( cash_hand_player_statistics.val_curr_conv != 0) then cash_hand_player_statistics.val_curr_conv*cash_hand_player_statistics.amt_won else 0.0 end)) as "amt_won_curr_conv", (cash_hand_player_statistics.id_final_hand) as "id_final_hand", (cash_hand_player_statistics.flg_showed) as "flg_showed", (cash_hand_player_statistics.enum_folded) as "enum_folded", (cash_hand_player_statistics.holecard_1) as "id_holecard1", (cash_hand_player_statistics.holecard_2) as "id_holecard2", (cash_hand_player_statistics.holecard_3) as "id_holecard3", (cash_hand_player_statistics.holecard_4) as "id_holecard4", ((case when(cash_hand_player_statistics.flg_f_saw and cash_hand_summary.cnt_players_f = 2) then (select chps.holecard_1 from cash_hand_player_statistics chps where chps.id_hand = cash_hand_player_statistics.id_hand and chps.id_limit = cash_hand_player_statistics.id_limit and chps.position != cash_hand_player_statistics.position and chps.flg_f_saw) else 0 end)) as "villain_id_holecard1", ((case when(cash_hand_player_statistics.flg_f_saw and cash_hand_summary.cnt_players_f = 2) then (select chps.holecard_2 from cash_hand_player_statistics chps where chps.id_hand = cash_hand_player_statistics.id_hand and chps.id_limit = cash_hand_player_statistics.id_limit and chps.position != cash_hand_player_statistics.position and chps.flg_f_saw) else 0 end)) as "villain_id_holecard2", ((case when(cash_hand_player_statistics.flg_f_saw and cash_hand_summary.cnt_players_f = 2) then (select chps.holecard_3 from cash_hand_player_statistics chps where chps.id_hand = cash_hand_player_statistics.id_hand and chps.id_limit = cash_hand_player_statistics.id_limit and chps.position != cash_hand_player_statistics.position and chps.flg_f_saw) else 0 end)) as "villain_id_holecard3", ((case when(cash_hand_player_statistics.flg_f_saw and cash_hand_summary.cnt_players_f = 2) then (select chps.holecard_4 from cash_hand_player_statistics chps where chps.id_hand = cash_hand_player_statistics.id_hand and chps.id_limit = cash_hand_player_statistics.id_limit and chps.position != cash_hand_player_statistics.position and chps.flg_f_saw) else 0 end)) as "villain_id_holecard4", (lookup_actions_p.action) as "str_actions_p", (cash_hand_summary.card_1) as "id_flop1", (cash_hand_summary.card_2) as "id_flop2", (cash_hand_summary.card_3) as "id_flop3", (lookup_actions_f.action) as "str_actions_f", (cash_hand_summary.card_4) as "id_turn", (lookup_actions_t.action) as "str_actions_t", (cash_hand_summary.card_5) as "id_river", (lookup_actions_r.action) as "str_actions_r", (player_winner.player_name) as "str_winner", (cash_hand_summary.id_win_hand) as "id_win_hand", (cash_hand_summary.amt_pot) as "amt_pot", (cash_limit.limit_currency) as "limit_currency", (cash_hand_summary.amt_rake) as "amt_rake" FROM cash_hand_player_statistics , cash_hand_summary, player, cash_limit, lookup_actions lookup_actions_p, lookup_actions lookup_actions_f, lookup_actions lookup_actions_t, lookup_actions lookup_actions_r, player player_winner WHERE (cash_hand_summary.id_hand = cash_hand_player_statistics.id_hand AND cash_hand_summary.id_limit = cash_hand_player_statistics.id_limit) AND (player.id_player = cash_hand_player_statistics.id_player) AND (cash_limit.id_limit = cash_hand_player_statistics.id_limit) AND (lookup_actions_p.id_action=cash_hand_player_statistics.id_action_p) AND (lookup_actions_f.id_action=cash_hand_player_statistics.id_action_f) AND (lookup_actions_t.id_action=cash_hand_player_statistics.id_action_t) AND (lookup_actions_r.id_action=cash_hand_player_statistics.id_action_r) AND (cash_limit.id_limit = cash_hand_summary.id_limit) AND (player_winner.id_player = cash_hand_summary.id_winner) AND (((1=1))AND (cash_hand_player_statistics.id_gametype = 1)AND (cash_hand_player_statistics.id_gametype<>1 OR (cash_hand_player_statistics.id_gametype=1 AND (cash_hand_player_statistics.id_limit in (SELECT hlrl.id_limit FROM cash_limit hlrl WHERE (hlrl.flg_nlpl=false and (CASE WHEN hlrl.limit_currency='SEK' THEN (hlrl.amt_bb*0.15) ELSE (CASE WHEN hlrl.limit_currency='INR' THEN (hlrl.amt_bb*0.020) ELSE (CASE WHEN hlrl.limit_currency='XSC' THEN 0.0 ELSE (CASE WHEN hlrl.limit_currency='PLY' THEN 0.0 ELSE hlrl.amt_bb END) END) END) END)<=1.01) or (hlrl.flg_nlpl=true and (CASE WHEN hlrl.limit_currency='SEK' THEN (hlrl.amt_bb*0.15) ELSE (CASE WHEN hlrl.limit_currency='INR' THEN (hlrl.amt_bb*0.020) ELSE (CASE WHEN hlrl.limit_currency='XSC' THEN 0.0 ELSE (CASE WHEN hlrl.limit_currency='PLY' THEN 0.0 ELSE hlrl.amt_bb END) END) END) END)<=0.51)))))) group by chps.id_hand, chps.id_player having ((((sum(cc.cnt_hands))) > 1000))))) ORDER BY (timezone('UTC', cash_hand_player_statistics.date_played + INTERVAL '0 HOURS')) desc LIMIT 1000

Error: Unable to execute query: Fatal Error; Reason: Error: (ERROR: syntax error at or near ")" LINE 1: ....id_player having ((((sum(cc.cnt_hands))) > 1000))))) ORDER... ^ )

I added a spurious expression to isolate the anti-piracy expression to be able to use your expression but after experimenting I decided it didn't suit my needs.

I came up with a much faster solution based on PT4's built-in Active Player Hand Report.

Example filter expression for the most recent 1K hands played on the most recent day where BTN saw the flop HU having played > 1K hands when >= 3 players were dealt into the hand:

Code: Select all

cash_hand_player_statistics.date_played >= (select (date_trunc('day', chs.date_played)) from cash_hand_summary chs order by chs.date_played desc limit 1) and cash_hand_player_statistics.date_played <= (select (date_trunc('day', chs.date_played) + time '23:59:59') from cash_hand_summary chs order by chs.date_played desc limit 1) and cash_hand_player_statistics.position = 0 and cash_hand_summary.id_hand in (select chs.id_hand from cash_hand_summary chs where chs.date_played >= (select (date_trunc('day', chs.date_played)) from cash_hand_summary chs order by chs.date_played desc limit 1) and chs.date_played <= (select (date_trunc('day', chs.date_played) + time '23:59:59') from cash_hand_summary chs order by chs.date_played desc limit 1) and chs.id_hand in (select chps.id_hand from cash_hand_player_statistics chps where chps.id_hand = chs.id_hand and chps.id_limit = chs.id_limit and chps.position = 0 and chps.flg_f_saw and chs.cnt_players_f = 2 and chps.id_player in (select cc.id_player from cash_cache cc where cc.id_player = chps.id_player and cc.cnt_players >= 3 group by cc.id_player having sum(cc.cnt_hands) > 1000)) order by chs.date_played desc limit 1000)

An offset can be added to the limit at the end of the expression. For example limit 5000 offset 1000 would return 5K hands after skipping 1K hands.

It's better to specify the appropriate "Most Recent X Hands" in My Reports instead of "All Hands" otherwise Pg's planner might use a Nested Loop Semi Join instead of a Hash Semi Join.

Here is what it looks like in PgAdmin:

I also added a new stat to the report called Villain Hole Cards based on the built-in Hole Cards stat. Expression for the villain_id_holecard1 column:

Code: Select all

if[cash_hand_player_statistics.flg_f_saw and cash_hand_summary.cnt_players_f = 2, (select chps.holecard_1 from cash_hand_player_statistics chps where chps.id_hand = cash_hand_player_statistics.id_hand and chps.id_limit = cash_hand_player_statistics.id_limit and chps.position != cash_hand_player_statistics.position and chps.flg_f_saw), 0]

Duplicate for villain_id_holecard2 and change it to use chps.holecard_2 then do the same for 3 and 4 and duplicate the built-in Hole Cards stat and modify it to use villain_id_holecard1 etc.