sssstatisticLucky Coin Profit
    Updated 2024-05-21
    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 event_name in (
    'NewTable',
    'Play',
    'Claim'
    )
    GROUP BY decoded_log['tableId']
    order by tableId DESC
    ), info AS (
    -- winner
    SELECT winner as address,
    cost * 90 / 100 AS reward,
    cost
    FROM tables tb
    WHERE winner is not NULL
    UNION ALL
    -- unlucky
    SELECT CASE
    WHEN winner = player then host
    ELSE player
    END AS address,
    cost * -1 AS reward,
    cost
    FROM tables
    )
    select address,
    sum(cost) AS Capital,
    sum(reward) AS Interest,
    QueryRunArchived: QueryRun has been archived