-- Lucky Coin 500 points per 10 billion Interest

SELECT

decoded_log['tableId'] AS tableId,

MAX(decoded_log['host']) AS host,

MAX(decoded_log['player']) AS player,

MAX(decoded_log['winner']) AS winner,

MAX(decoded_log['cost'] / POW(10, 18)) AS cost,

MAX(decoded_log['reward'] / POW(10, 18)) AS reward

FROM

blast.core.ez_decoded_event_logs el

WHERE

contract_address = '0xaf466677d96e6df2e31a86fda7e54a31d64647ed'

AND event_name IN ('NewTable', 'Play', 'Claim')

GROUP BY

decoded_log['tableId']

),

info AS (

-- winner

SELECT

winner AS address,

SUM(cost * 90 / 100) AS reward,

MAX(cost) AS cost,

COUNT(*) AS times

FROM

tables tb

WHERE

winner IS NOT NULL

GROUP BY

winner

UNION ALL -- unlucky

SELECT

CASE

WHEN winner = player THEN host

ELSE player

END AS address,