WITH tables AS (

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

where contract_address = '0xaf466677d96e6df2e31a86fda7e54a31d64647ed'

and block_timestamp BETWEEN '2024-10-11 03:00:00' and '2024-10-28 03:00:00'

and event_name in (

'NewTable',

'Play',

'Claim'

)

GROUP BY decoded_log['tableId']

order by tableId DESC

), 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,