sssstatisticLucky Coin: Blast Gold Event (11th Oct 3 AM UTC - 28th Oct 3 AM UTC)
    Updated 2024-12-28
    -- forked from TOP Lucky Coin @ https://flipsidecrypto.xyz/studio/queries/d121a381-d844-4947-82c4-918e7dbf2810

    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,
    sum(cost * -1) AS reward,
    sum(cost) as cost,
    Last run: about 2 months ago
    ADDRESS
    PROFIT
    Play Times
    1
    0x04c9208613e3386802a374c23b064a7f20640321
    12293999200016
    2
    0x07a83dbd5930ec4d96a28e78302cbad262525298
    11189240000062
    3
    0x438526cc58c30d17793a7996e71dba832224fe3c
    91386499999.821
    4
    0xe379f51d31a754d01a440819c384ba5126bbbd96
    8669900000014
    5
    0xaf6b6713724a987e36e7ab7d16020e9c5271b736
    8643000000012
    6
    0x06fb7dcd4ae4bfdd5fe9c3a20bbc43165c93db94
    7730000000013
    7
    0x0f1e1c9da8e9aa0a5ea890dadaadefcda541795a
    5835000000019
    8
    0x765aa3116c46aec2aa743514597cedb82baa412a
    418700000007
    9
    0x07d18e0241e79cbc3c81ba61cdd061580f8409c5
    221503000008
    10
    0xcdd5e00124e4f20f63e7ea8416369683b4618947
    180000000002
    11
    0xe747a8b7f7355525385cfca8b288d6b11296d89a
    16674699999.618
    12
    0x4680f95dbabc0e39daea05a80c10c47c2d6f2975
    154800000004
    13
    0x62ec1636785797e3c312197a108c018448e72736
    135000000002
    14
    0xfa585be0a6a84bd1f80a605767440cff05ab0588
    116850000003
    15
    0xbb116038c2b5a755ab35870a2c27eaa22f941e08
    101700000002
    16
    0x852ad1e8d61fb0a3328fc80158f735aa67317c36
    99000000002
    17
    0xa2e9783e5a942700b6c73fbd8ae1bc9a399d5ee8
    99000000002
    18
    0x75e6d392c9f3a81c85e63407ccb69847b13d1aef
    99000000002
    19
    0x30de5f5b30fddb632b0808a6c261dd70937b950c
    99000000002
    20
    0x7e61791e91df8ad6c8a5567efd49153c03125a0d
    99000000002
    ...
    1154
    67KB
    3s