Challenge 24
20/10/26 07:44
Challenge
The original challenge description is not very clear concerning the low Straight (A, 2, 3, 4, 5),
so we propose here a solution covering both situations: you control the behavior by defining the string variable :with_low_straight
that should contain a boolean value "true|false"
WITH hands(js) AS (
SELECT q'#{
"poker_hands": [
{
"hand_id": 1,
"cards": [
{ "rank": "seven", "suit": "Hearts" },{ "rank": "Jack", "suit": "Hearts" },{ "rank": "three", "suit": "Clubs" },{ "rank": "Jack", "suit": "Diamonds" },{ "rank": "nine", "suit": "Diamonds" }
]
},
{
"hand_id": 2,
"cards": [
{ "rank": "five", "suit": "Diamonds" },{ "rank": "King", "suit": "Diamonds" },{ "rank": "six", "suit": "Diamonds" },{ "rank": "eight", "suit": "Hearts" },{ "rank": "ten", "suit": "Hearts" }
]
},
{
"hand_id": 3,
"cards": [
{ "rank": "King", "suit": "Spades" },
{ "rank": "seven", "suit": "Hearts" },
{ "rank": "Queen", "suit": "Hearts" },
{ "rank": "five", "suit": "Clubs" },
{ "rank": "nine", "suit": "Spades" }
]
},
{
"hand_id": 4,
"cards": [
{ "rank": "Queen", "suit": "Clubs" },
{ "rank": "Jack", "suit": "Diamonds" },
{ "rank": "Queen", "suit": "Diamonds" },
{ "rank": "four", "suit": "Spades" },
{ "rank": "Queen", "suit": "Spades" }
]
},
{
"hand_id": 5,
"cards": [
{ "rank": "Jack", "suit": "Diamonds" },
{ "rank": "ten", "suit": "Diamonds" },
{ "rank": "Queen", "suit": "Diamonds" },
{ "rank": "King", "suit": "Diamonds" },
{ "rank": "Ace", "suit": "Diamonds" }
]
},
{
"hand_id": 6,
"cards": [
{ "rank": "three", "suit": "Hearts" },
{ "rank": "Jack", "suit": "Clubs" },
{ "rank": "six", "suit": "Clubs" },
{ "rank": "six", "suit": "Spades" },
{ "rank": "six", "suit": "Diamonds" }
]
},
{
"hand_id": 7,
"cards": [
{ "rank": "Queen", "suit": "Diamonds" },
{ "rank": "Ace", "suit": "Diamonds" },
{ "rank": "seven", "suit": "Clubs" },
{ "rank": "ten", "suit": "Hearts" },
{ "rank": "five", "suit": "Spades" }
]
},
{
"hand_id": 8,
"cards": [
{ "rank": "nine", "suit": "Hearts" },
{ "rank": "four", "suit": "Spades" },
{ "rank": "eight", "suit": "Hearts" },
{ "rank": "five", "suit": "Diamonds" },
{ "rank": "nine", "suit": "Spades" }
]
},
{
"hand_id": 9,
"cards": [
{ "rank": "ten", "suit": "Spades" },
{ "rank": "ten", "suit": "Clubs" },
{ "rank": "three", "suit": "Clubs" },
{ "rank": "seven", "suit": "Hearts" },
{ "rank": "seven", "suit": "Clubs" }
]
},
{
"hand_id": 10,
"cards": [
{ "rank": "King", "suit": "Hearts" },
{ "rank": "five", "suit": "Spades" },
{ "rank": "five", "suit": "Hearts" },
{ "rank": "two", "suit": "Clubs" },
{ "rank": "Jack", "suit": "Hearts" }
]
},
{
"hand_id": 11,
"cards": [
{ "rank": "ten", "suit": "Hearts" },
{ "rank": "eight", "suit": "Clubs" },
{ "rank": "nine", "suit": "Spades" },
{ "rank": "Jack", "suit": "Diamonds" },
{ "rank": "Queen", "suit": "Spades" }
]
},
{
"hand_id": 12,
"cards": [
{ "rank": "ten", "suit": "Hearts" },
{ "rank": "eight", "suit": "Spades" },
{ "rank": "eight", "suit": "Diamonds" },
{ "rank": "Jack", "suit": "Clubs" },
{ "rank": "Jack", "suit": "Hearts" }
]
},
{
"hand_id": 13,
"cards": [
{ "rank": "King", "suit": "Spades" },
{ "rank": "King", "suit": "Diamonds" },
{ "rank": "two", "suit": "Spades" },
{ "rank": "two", "suit": "Clubs" },
{ "rank": "ten", "suit": "Diamonds" }
]
},
{
"hand_id": 14,
"cards": [
{ "rank": "six", "suit": "Hearts" },
{ "rank": "eight", "suit": "Spades" },
{ "rank": "two", "suit": "Diamonds" },
{ "rank": "six", "suit": "Clubs" },
{ "rank": "four", "suit": "Hearts" }
]
},
{
"hand_id": 15,
"cards": [
{ "rank": "four", "suit": "Spades" },
{ "rank": "Jack", "suit": "Hearts" },
{ "rank": "six", "suit": "Clubs" },
{ "rank": "Jack", "suit": "Diamonds" },
{ "rank": "four", "suit": "Hearts" }
]
},
{
"hand_id": 16,
"cards": [
{ "rank": "Ace", "suit": "Clubs" },
{ "rank": "four", "suit": "Clubs" },
{ "rank": "Jack", "suit": "Diamonds" },
{ "rank": "eight", "suit": "Clubs" },
{ "rank": "four", "suit": "Hearts" }
]
},
{
"hand_id": 17,
"cards": [ { "rank": "two", "suit": "Diamonds" },
{ "rank": "eight", "suit": "Spades" },
{ "rank": "four", "suit": "Diamonds" },
{ "rank": "Queen", "suit": "Spades" },
{ "rank": "six", "suit": "Spades" }
]
},
{
"hand_id": 18,
"cards": [
{ "rank": "Queen", "suit": "Clubs" },
{ "rank": "Jack", "suit": "Clubs" },
{ "rank": "ten", "suit": "Diamonds" },
{ "rank": "Jack", "suit": "Hearts" },
{ "rank": "Jack", "suit": "Spades" }
]
},
{
"hand_id": 19,
"cards": [
{ "rank": "eight", "suit": "Spades" },
{ "rank": "seven", "suit": "Spades" },
{ "rank": "seven", "suit": "Clubs" },
{ "rank": "Queen", "suit": "Clubs" },
{ "rank": "seven", "suit": "Diamonds" } ]
},
{
"hand_id": 20,
"cards": [
{ "rank": "Ace", "suit": "Spades" },
{ "rank": "ten", "suit": "Clubs" },
{ "rank": "King", "suit": "Spades" },
{ "rank": "ten", "suit": "Diamonds" },
{ "rank": "seven", "suit": "Spades" } ]
},
{
"hand_id": 21,
"cards": [
{ "rank": "ten", "suit": "Hearts" },
{ "rank": "King", "suit": "Spades" },
{ "rank": "Ace", "suit": "Diamonds" },
{ "rank": "Queen", "suit": "Diamonds" },
{ "rank": "Jack", "suit": "Diamonds" }
]
},
{
"hand_id": 22,
"cards": [
{ "rank": "Queen", "suit": "Hearts" },
{ "rank": "five", "suit": "Clubs" },
{ "rank": "eight", "suit": "Clubs" },
{ "rank": "Jack", "suit": "Hearts" },
{ "rank": "three", "suit": "Spades" } ]
},
{
"hand_id": 23,
"cards": [
{ "rank": "six", "suit": "Diamonds" },
{ "rank": "six", "suit": "Spades" },
{ "rank": "nine", "suit": "Diamonds" },
{ "rank": "ten", "suit": "Diamonds" },
{ "rank": "six", "suit": "Clubs" } ]
},
{
"hand_id": 24,
"cards": [
{ "rank": "Queen", "suit": "Clubs" },
{ "rank": "King", "suit": "Clubs" },
{ "rank": "Ace", "suit": "Hearts" },
{ "rank": "Jack", "suit": "Spades" },
{ "rank": "ten", "suit": "Hearts" } ]
},
{
"hand_id": 25,
"cards": [
{ "rank": "three", "suit": "Clubs" },
{ "rank": "seven", "suit": "Clubs" },
{ "rank": "ten", "suit": "Diamonds" },
{ "rank": "Queen", "suit": "Hearts" },
{ "rank": "three", "suit": "Spades" }
]
},
{
"hand_id": 26, "cards": [ { "rank": "ten", "suit": "Spades" },
{ "rank": "five", "suit": "Spades" },
{ "rank": "Queen", "suit": "Hearts" },
{ "rank": "King", "suit": "Spades" },
{ "rank": "Jack", "suit": "Diamonds" } ]
},
{
"hand_id": 27, "cards": [
{ "rank": "nine", "suit": "Clubs" },
{ "rank": "seven", "suit": "Diamonds" },
{ "rank": "two", "suit": "Clubs" },
{ "rank": "seven", "suit": "Spades" },
{ "rank": "two", "suit": "Hearts" } ]
},
{
"hand_id": 28,
"cards": [
{ "rank": "six", "suit": "Hearts" },
{ "rank": "five", "suit": "Hearts" },
{ "rank": "ten", "suit": "Diamonds" },
{ "rank": "six", "suit": "Spades" },
{ "rank": "Ace", "suit": "Hearts" }
]
},
{
"hand_id": 29,
"cards": [
{ "rank": "seven", "suit": "Diamonds" },
{ "rank": "four", "suit": "Clubs" },
{ "rank": "Queen", "suit": "Diamonds" },
{ "rank": "eight", "suit": "Clubs" },
{ "rank": "eight", "suit": "Hearts" } ]
},
{
"hand_id": 30,
"cards": [
{ "rank": "seven", "suit": "Hearts" },
{ "rank": "Jack", "suit": "Clubs" },
{ "rank": "ten", "suit": "Clubs" },
{ "rank": "Ace", "suit": "Clubs" },
{ "rank": "King", "suit": "Spades" } ]
},
{
"hand_id": 31,
"cards": [
{ "rank": "eight", "suit": "Hearts" },
{ "rank": "Ace", "suit": "Hearts" },
{ "rank": "King", "suit": "Diamonds" },
{ "rank": "nine", "suit": "Diamonds" },
{ "rank": "Ace", "suit": "Spades" } ]
},
{
"hand_id": 32,
"cards": [
{ "rank": "five", "suit": "Diamonds" },
{ "rank": "seven", "suit": "Clubs" },
{ "rank": "Ace", "suit": "Diamonds" },
{ "rank": "five", "suit": "Hearts" },
{ "rank": "Queen", "suit": "Hearts" } ]
},
{
"hand_id": 33,
"cards": [
{ "rank": "ten", "suit": "Hearts" },
{ "rank": "six", "suit": "Clubs" },
{ "rank": "two", "suit": "Clubs" },
{ "rank": "eight", "suit": "Spades" },
{ "rank": "five", "suit": "Diamonds" } ]
},
{
"hand_id": 34,
"cards": [
{ "rank": "eight", "suit": "Spades" },
{ "rank": "four", "suit": "Spades" },
{ "rank": "seven", "suit": "Spades" },
{ "rank": "five", "suit": "Spades" },
{ "rank": "six", "suit": "Spades" }
]
},
{
"hand_id": 35,
"cards": [
{ "rank": "nine", "suit": "Hearts" },
{ "rank": "Ace", "suit": "Hearts" },
{ "rank": "Jack", "suit": "Spades" },
{ "rank": "two", "suit": "Hearts" },
{ "rank": "nine", "suit": "Diamonds" }
]
},
{
"hand_id": 36,
"cards": [
{ "rank": "three", "suit": "Clubs" },
{ "rank": "two", "suit": "Clubs" },
{ "rank": "Ace", "suit": "Clubs" },
{ "rank": "five", "suit": "Clubs" },
{ "rank": "four", "suit": "Clubs" }
]
},
{
"hand_id": 37,
"cards": [
{ "rank": "seven", "suit": "Hearts" },
{ "rank": "Queen", "suit": "Hearts" },
{ "rank": "three", "suit": "Diamonds" },
{ "rank": "Queen", "suit": "Clubs" },
{ "rank": "seven", "suit": "Clubs" }
]
},
{
"hand_id": 38,
"cards": [
{ "rank": "Jack", "suit": "Hearts" },
{ "rank": "five", "suit": "Diamonds" },
{ "rank": "Ace", "suit": "Diamonds" },
{ "rank": "Jack", "suit": "Clubs" },
{ "rank": "Jack", "suit": "Diamonds" }
]
},
{
"hand_id": 39,
"cards": [
{ "rank": "Ace", "suit": "Spades" },
{ "rank": "two", "suit": "Hearts" },
{ "rank": "eight", "suit": "Clubs" },
{ "rank": "ten", "suit": "Diamonds" },
{ "rank": "Jack", "suit": "Hearts" }
]
},
{
"hand_id": 40, "cards": [
{ "rank": "Ace", "suit": "Hearts" },
{ "rank": "ten", "suit": "Hearts" },
{ "rank": "King", "suit": "Hearts" },
{ "rank": "Jack", "suit": "Hearts" },
{ "rank": "Queen", "suit": "Hearts" }
]
},
{
"hand_id": 41,
"cards": [
{ "rank": "eight", "suit": "Clubs" },
{ "rank": "Queen", "suit": "Hearts" },
{ "rank": "two", "suit": "Hearts" },
{ "rank": "four", "suit": "Spades" },
{ "rank": "Queen", "suit": "Diamonds" }
]
},
{
"hand_id": 42,
"cards": [
{ "rank": "three", "suit": "Diamonds" },
{ "rank": "five", "suit": "Diamonds" },
{ "rank": "six", "suit": "Diamonds" },
{ "rank": "four", "suit": "Diamonds" },
{ "rank": "seven", "suit": "Diamonds" }
]
},
{
"hand_id": 43,
"cards": [
{ "rank": "Ace", "suit": "Diamonds" },
{ "rank": "four", "suit": "Clubs" },
{ "rank": "three", "suit": "Hearts" },
{ "rank": "two", "suit": "Diamonds" },
{ "rank": "five", "suit": "Spades" }
]
},
{
"hand_id": 44,
"cards": [
{ "rank": "Ace", "suit": "Hearts" },
{ "rank": "nine", "suit": "Spades" },
{ "rank": "Jack", "suit": "Spades" },
{ "rank": "Jack", "suit": "Hearts" },
{ "rank": "seven", "suit": "Hearts" }
]
},
{
"hand_id": 45,
"cards": [
{ "rank": "Queen", "suit": "Spades" },
{ "rank": "Jack", "suit": "Hearts" },
{ "rank": "eight", "suit": "Hearts" },
{ "rank": "nine", "suit": "Spades" },
{ "rank": "ten", "suit": "Clubs" }
]
},
{
"hand_id": 46,
"cards": [
{ "rank": "nine", "suit": "Spades" },
{ "rank": "nine", "suit": "Hearts" },
{ "rank": "Jack", "suit": "Clubs" },
{ "rank": "four", "suit": "Spades" },
{ "rank": "two", "suit": "Clubs" }
]
},
{
"hand_id": 47,
"cards": [
{ "rank": "five", "suit": "Hearts" },
{ "rank": "four", "suit": "Diamonds" },
{ "rank": "seven", "suit": "Spades" },
{ "rank": "ten", "suit": "Clubs" },
{ "rank": "Ace", "suit": "Spades" }
]
},
{
"hand_id": 48,
"cards": [
{ "rank": "Jack", "suit": "Spades" },
{ "rank": "Jack", "suit": "Clubs" },
{ "rank": "Jack", "suit": "Diamonds" },
{ "rank": "three", "suit": "Clubs" },
{ "rank": "Jack", "suit": "Hearts" }
]
},
{
"hand_id": 49,
"cards": [
{ "rank": "Ace", "suit": "Spades" },
{ "rank": "five", "suit": "Hearts" },
{ "rank": "Jack", "suit": "Spades" },
{ "rank": "Ace", "suit": "Diamonds" },
{ "rank": "King", "suit": "Hearts" }
]
},
{
"hand_id": 50,
"cards": [
{ "rank": "eight", "suit": "Clubs" },
{ "rank": "Jack", "suit": "Clubs" },
{ "rank": "King", "suit": "Hearts" },
{ "rank": "Jack", "suit": "Hearts" },
{ "rank": "Jack", "suit": "Spades" }
]
},
{
"hand_id": 51,
"cards": [
{ "rank": "nine", "suit": "Diamonds" },
{ "rank": "Ace", "suit": "Hearts" },
{ "rank": "five", "suit": "Spades" },
{ "rank": "Jack", "suit": "Diamonds" },
{ "rank": "two", "suit": "Spades" }
]
},
{
"hand_id": 52,
"cards": [
{ "rank": "Queen", "suit": "Spades" },
{ "rank": "seven", "suit": "Spades" },
{ "rank": "two", "suit": "Hearts" },
{ "rank": "seven", "suit": "Diamonds" },
{ "rank": "Ace", "suit": "Diamonds" }
]
},
{
"hand_id": 53,
"cards": [
{ "rank": "ten", "suit": "Clubs" },
{ "rank": "six", "suit": "Clubs" },
{ "rank": "six", "suit": "Diamonds" },
{ "rank": "ten", "suit": "Hearts" },
{ "rank": "Ace", "suit": "Diamonds" }
]
},
{
"hand_id": 54,
"cards": [
{ "rank": "three", "suit": "Spades" },
{ "rank": "King", "suit": "Clubs" },
{ "rank": "three", "suit": "Diamonds" },
{ "rank": "four", "suit": "Diamonds" },
{ "rank": "three", "suit": "Hearts" }
]
},
{
"hand_id": 55,
"cards": [
{ "rank": "five", "suit": "Hearts" },
{ "rank": "six", "suit": "Hearts" },
{ "rank": "eight", "suit": "Hearts" },
{ "rank": "seven", "suit": "Hearts" },
{ "rank": "four", "suit": "Hearts" }
]
},
{
"hand_id": 56,
"cards": [
{ "rank": "Jack", "suit": "Spades" },
{ "rank": "Jack", "suit": "Diamonds" },
{ "rank": "ten", "suit": "Hearts" },
{ "rank": "Jack", "suit": "Clubs" },
{ "rank": "ten", "suit": "Spades" }
]
},
{
"hand_id": 57,
"cards": [
{ "rank": "five", "suit": "Hearts" },
{ "rank": "two", "suit": "Hearts" },
{ "rank": "King", "suit": "Hearts" },
{ "rank": "six", "suit": "Hearts" },
{ "rank": "four", "suit": "Hearts" }
]
},
{
"hand_id": 58,
"cards": [
{ "rank": "five", "suit": "Hearts" },
{ "rank": "Queen", "suit": "Diamonds" },
{ "rank": "King", "suit": "Clubs" },
{ "rank": "five", "suit": "Spades" },
{ "rank": "King", "suit": "Diamonds" }
]
},
{
"hand_id": 59,
"cards": [
{ "rank": "Ace", "suit": "Spades" },
{ "rank": "seven", "suit": "Spades" },
{ "rank": "Ace", "suit": "Hearts" },
{ "rank": "Jack", "suit": "Hearts" },
{ "rank": "four", "suit": "Hearts" }
]
},
{
"hand_id": 60,
"cards": [
{ "rank": "King", "suit": "Clubs" },
{ "rank": "King", "suit": "Diamonds" },
{ "rank": "five", "suit": "Diamonds" },
{ "rank": "two", "suit": "Hearts" },
{ "rank": "King", "suit": "Spades" }
]
},
{
"hand_id": 61,
"cards": [
{ "rank": "Queen", "suit": "Spades" },
{ "rank": "ten", "suit": "Hearts" },
{ "rank": "King", "suit": "Hearts" },
{ "rank": "Ace", "suit": "Clubs" },
{ "rank": "Jack", "suit": "Diamonds" }
]
},
{
"hand_id": 62,
"cards": [
{ "rank": "six", "suit": "Hearts" },
{ "rank": "four", "suit": "Hearts" },
{ "rank": "Jack", "suit": "Clubs" },
{ "rank": "Ace", "suit": "Hearts" },
{ "rank": "seven", "suit": "Diamonds" }
]
},
{
"hand_id": 63,
"cards": [
{ "rank": "Jack", "suit": "Hearts" },
{ "rank": "Queen", "suit": "Diamonds" },
{ "rank": "ten", "suit": "Spades" },
{ "rank": "Ace", "suit": "Spades" },
{ "rank": "King", "suit": "Clubs" }
]
},
{
"hand_id": 64, "cards": [ { "rank": "seven", "suit": "Clubs" },
{ "rank": "Queen", "suit": "Clubs" },
{ "rank": "Queen", "suit": "Hearts" },
{ "rank": "Jack", "suit": "Clubs" },
{ "rank": "six", "suit": "Hearts" }
]
},
{
"hand_id": 65,
"cards": [
{ "rank": "Jack", "suit": "Spades" },
{ "rank": "five", "suit": "Clubs" },
{ "rank": "Jack", "suit": "Hearts" },
{ "rank": "King", "suit": "Diamonds" },
{ "rank": "two", "suit": "Hearts" }
]
},
{
"hand_id": 66,
"cards": [
{ "rank": "eight", "suit": "Diamonds" },
{ "rank": "Jack", "suit": "Hearts" },
{ "rank": "Jack", "suit": "Spades" },
{ "rank": "five", "suit": "Hearts" },
{ "rank": "Jack", "suit": "Diamonds" }
]
},
{
"hand_id": 67,
"cards": [
{ "rank": "eight", "suit": "Clubs" },
{ "rank": "nine", "suit": "Clubs" },
{ "rank": "ten", "suit": "Clubs" },
{ "rank": "Queen", "suit": "Clubs" },
{ "rank": "Jack", "suit": "Clubs" }
]
},
{
"hand_id": 68,
"cards": [
{ "rank": "eight", "suit": "Spades" },
{ "rank": "four", "suit": "Diamonds" },
{ "rank": "Ace", "suit": "Clubs" },
{ "rank": "nine", "suit": "Clubs" },
{ "rank": "nine", "suit": "Diamonds" }
]
},
{
"hand_id": 69,
"cards": [
{ "rank": "four", "suit": "Hearts" },
{ "rank": "eight", "suit": "Hearts" },
{ "rank": "Ace", "suit": "Hearts" },
{ "rank": "five", "suit": "Hearts" },
{ "rank": "Jack", "suit": "Hearts" }
]
},
{
"hand_id": 70,
"cards": [
{ "rank": "eight", "suit": "Hearts" },
{ "rank": "four", "suit": "Hearts" },
{ "rank": "two", "suit": "Hearts" },
{ "rank": "six", "suit": "Hearts" },
{ "rank": "King", "suit": "Hearts" }
]
},
{
"hand_id": 71,
"cards": [
{ "rank": "five", "suit": "Spades" },
{ "rank": "two", "suit": "Clubs" },
{ "rank": "five", "suit": "Clubs" },
{ "rank": "Jack", "suit": "Hearts" },
{ "rank": "ten", "suit": "Hearts" }
]
},
{
"hand_id": 72,
"cards": [
{ "rank": "King", "suit": "Spades" },
{ "rank": "Jack", "suit": "Hearts" },
{ "rank": "King", "suit": "Hearts" },
{ "rank": "King", "suit": "Clubs" },
{ "rank": "Jack", "suit": "Spades" }
]
},
{
"hand_id": 73,
"cards": [
{ "rank": "Queen", "suit": "Spades" },
{ "rank": "ten", "suit": "Spades" },
{ "rank": "Jack", "suit": "Spades" },
{ "rank": "King", "suit": "Spades" },
{ "rank": "Ace", "suit": "Spades" }
]
},
{
"hand_id": 74,
"cards": [
{ "rank": "five", "suit": "Clubs" },
{ "rank": "seven", "suit": "Diamonds" },
{ "rank": "King", "suit": "Hearts" },
{ "rank": "Jack", "suit": "Spades" },
{ "rank": "five", "suit": "Hearts" }
]
},
{
"hand_id": 75,
"cards": [
{ "rank": "Queen", "suit": "Clubs" },
{ "rank": "three", "suit": "Spades" },
{ "rank": "two", "suit": "Hearts" },
{ "rank": "eight", "suit": "Hearts" },
{ "rank": "Queen", "suit": "Hearts" }
]
},
{
"hand_id": 76,
"cards": [
{ "rank": "Jack", "suit": "Hearts" },
{ "rank": "seven", "suit": "Diamonds" },
{ "rank": "Jack", "suit": "Spades" },
{ "rank": "six", "suit": "Spades" },
{ "rank": "Ace", "suit": "Spades" }
]
},
{
"hand_id": 77,
"cards": [
{ "rank": "seven", "suit": "Diamonds" },
{ "rank": "four", "suit": "Clubs" },
{ "rank": "Ace", "suit": "Clubs" },
{ "rank": "Ace", "suit": "Diamonds" },
{ "rank": "Jack", "suit": "Clubs" }
]
},
{
"hand_id": 78,
"cards": [
{ "rank": "ten", "suit": "Clubs" },
{ "rank": "three", "suit": "Diamonds" },
{ "rank": "Ace", "suit": "Spades" },
{ "rank": "Ace", "suit": "Diamonds" },
{ "rank": "Queen", "suit": "Diamonds" }
]
},
{
"hand_id": 79,
"cards": [
{ "rank": "two", "suit": "Clubs" },
{ "rank": "Jack", "suit": "Clubs" },
{ "rank": "six", "suit": "Diamonds" },
{ "rank": "eight", "suit": "Spades" },
{ "rank": "Queen", "suit": "Diamonds" }
]
},
{
"hand_id": 80,
"cards": [
{ "rank": "Jack", "suit": "Spades" },
{ "rank": "eight", "suit": "Diamonds" },
{ "rank": "nine", "suit": "Diamonds" },
{ "rank": "ten", "suit": "Hearts" },
{ "rank": "seven", "suit": "Diamonds" }
]
},
{
"hand_id": 81,
"cards": [
{ "rank": "King", "suit": "Hearts" },
{ "rank": "Ace", "suit": "Clubs" },
{ "rank": "three", "suit": "Diamonds" },
{ "rank": "King", "suit": "Clubs" },
{ "rank": "Ace", "suit": "Diamonds" }
]
},
{
"hand_id": 82,
"cards": [
{ "rank": "King", "suit": "Diamonds" },
{ "rank": "King", "suit": "Hearts" },
{ "rank": "seven", "suit": "Spades" },
{ "rank": "Queen", "suit": "Hearts" },
{ "rank": "King", "suit": "Clubs" }
]
}
]
}
#')
, ranks(name, id) AS (
VALUES
('two', 2),
('three', 3),
('four', 4),
('five', 5),
('six',6),
('seven', 7),
('eight', 8),
('nine', 9),
('ten', 10),
('Jack', 11),
('Queen', 12),
('King', 13),
('Ace', 14)
)
,hand_types(name, value, straight, colour, maxid, minid, nids, nsuits, min_n_per_id, max_n_per_id) AS (
VALUES
('Royal Flush', 512, true, true, 14, 10, 5, 1, null, null),
('Straight Flush', 256, true, true, -14, null, 5, 1, null, null),
('Straight Flush', 256, true, true, 14, 2, 5, 1, null, null),
('Four of a Kind', 128, false, false, null, null, 2, null, 1, 4),
('Full House', 64, false, false, null, null, 2, null, null, -4),
('Flush', 32, false, true, null, null, 5, 1, 1, 1),
('Straight', 16, true, false, null, null, null, null, null, null),
('Three of a Kind', 8, false, false, null, null, 3, null, 1, 3),
('Two Pair', 4, false, false, null, null, 3, null, 1, 2),
('Pair', 2, false, false, null, null, 4, null, null, null),
('High Card', 1, false, false, null, null, 5, null, 1, 1)
),
flat_hands AS (
SELECT t.*, r.id,
LISTAGG(decode(r.id, 11, 'J', 12, 'Q', 13, 'K', 14, 'A', TO_CHAR(r.id)) ||
DECODE(substr(suit, 1,1), 'D', '♦', 'S', '♠', 'H', '♥', 'C', '♣'), ' ')
WITHIN GROUP(ORDER BY id) OVER(PARTITION BY hand_id) AS hand
FROM hands h,
json_table(
h.js,
'$.poker_hands[*]'
columns (
hand_id number PATH '$.hand_id',
nested path '$.cards[*]'
columns (
rank PATH '$.rank',
suit PATH '$.suit'
)
)
) t
JOIN ranks r ON t.rank = r.name
)
,diagnostic AS (
SELECT d.*,
MIN(n_per_id) OVER(PARTITION BY hand_id) AS min_n_per_id,
MAX(n_per_id) OVER(PARTITION BY hand_id) AS max_n_per_id
FROM (
SELECT h.*,
COUNT(DISTINCT id) OVER(PARTITION BY hand_id) AS n_ids,
COUNT(DISTINCT suit) OVER(PARTITION BY hand_id) AS n_suits,
COUNT(*) OVER(PARTITION BY hand_id, id) AS n_per_id,
MIN(id) OVER(PARTITION BY hand_id) AS min_id,
MAX(id) OVER(PARTITION BY hand_id) AS max_id,
(MAX(id) OVER(PARTITION BY hand_id) - MIN(id) OVER(PARTITION BY hand_id)) = 4
AND COUNT(DISTINCT id) OVER(PARTITION BY hand_id) = 5 AS possible_straight,
(COUNT(DISTINCT suit) OVER(PARTITION BY hand_id) = 1) AS possible_colour
/* how to detect low straight A 2 3 4 5 ?*/
, (MAX(CASE WHEN id = 14 THEN 1 ELSE id END) OVER(PARTITION BY hand_id) -
MIN(CASE WHEN id = 14 THEN 1 ELSE id END) OVER(PARTITION BY hand_id)) = 4
AND MIN(CASE WHEN id = 14 THEN 1 ELSE id END) OVER(PARTITION BY hand_id) = 1
AND COUNT(DISTINCT id) OVER(PARTITION BY hand_id) = 5 AS possible_low_straight
FROM flat_hands h
) d
)
SELECT
NVL(TO_CHAR(hand_id), '-----------') AS id,
NVL(hand, '---------------->') AS cards,
NVL(name, 'Total') AS best_hand,
SUM(DECODE(CAST(:with_low_straight AS BOOLEAN), true, DECODE(rn, 1, value, 0), value)) AS value
FROM (
SELECT
DISTINCT d.hand_id, d.hand, ht.name, ht.value,
ROW_NUMBER() OVER(PARTITION BY d.hand_id ORDER BY value DESC) AS rn
FROM hand_types ht
JOIN diagnostic d on
ht.colour = d.possible_colour AND (d.possible_straight = ht.straight OR (CAST(:with_low_straight AS BOOLEAN) AND d.possible_low_straight = ht.straight))
AND NVL(ht.nsuits, d.n_suits) = d.n_suits
AND (NVL(ht.maxid,d.max_id) = d.max_id or (CASE WHEN ht.maxid < 0 THEN d.max_id < abs(ht.maxid) end))
AND (NVL(ht.minid,d.min_id) = d.min_id)
AND NVL(ht.nids, d.n_ids) = d.n_ids
AND (
NVL(ht.min_n_per_id, d.min_n_per_id) = d.min_n_per_id
AND
CASE WHEN ht.max_n_per_id < 0
THEN
d.max_n_per_id < abs(ht.max_n_per_id)
ELSE
NVL(ht.max_n_per_id, d.max_n_per_id) = d.max_n_per_id
END
)
QUALIFY( ROW_NUMBER() OVER(PARTITION BY d.hand_id ORDER BY value DESC) = 1 )
) d
GROUP BY GROUPING SETS((hand_id, name, hand), ())
ORDER BY d.hand_id NULLS FIRST, name
;