Hello all,
I am trying to construct a query which calculates my rakeback earned so far. My player id is 8 and I get 30% rakeback. Let me start with to say that I am pretty new to SQL. I read a few tutorials today and this is what I came up with.
My query is based on the following rakeback method: "Contributed - Rake per person is total rake in hand divided by number of players who contributed to the pot."
The following query selects for each game the numer of players who contributed to the pot:
Code:
SELECT game_id AS game_id,
COUNT(player_id) AS nr_of_players_who_contributed_to_pot
FROM game_players
WHERE total_bet > 0
GROUP BY game_id
Our next query selects all games I played in which the rake greater than 0:
Code:
SELECT game_players.game_id AS game_id,
game.rake AS rake
FROM game_players AS game_players,
game AS game
WHERE game_players.player_id = 8 AND
game.game_id = game_players.game_id AND
game.rake > 0
So if we combine both queries, I can calcuate my rake and rakeback:
Code:
SELECT total_rake_paid, total_rake_paid * 0.30
FROM
(
SELECT SUM(rake / nr_of_players_who_contributed_to_pot) AS total_rake_paid
FROM
(
SELECT game_players.game_id AS game_id,
game.rake AS rake
FROM game_players AS game_players,
game AS game
WHERE game_players.player_id = 8 AND
game.game_id = game_players.game_id AND
game.rake > 0
) t1,
(
SELECT game_id AS game_id,
COUNT(player_id) AS nr_of_players_who_contributed_to_pot
FROM game_players
WHERE total_bet > 0
GROUP BY game_id
) t2
WHERE t1.game_id = t2.game_id
) t
What do you guys think? As said, I'm pretty new to all this stuff, so can please someone confirm this query is correct.
FWIW, I have about 4700 hands in my database (100NL UB), my query says that I have paid $841 rake, which equals to $252 rakeback.
Thank you very much!
