banterlyticspnk-banana
    Updated 2024-02-17
    with boundaries as
    (select
    ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[0])::integer as season,
    ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[1])/((pow(10,18))) as prize,
    block_timestamp,
    COALESCE(LEAD(block_timestamp) OVER (ORDER BY block_timestamp),to_timestamp(100000000000)) as next_timestamp
    from polygon.core.fact_event_logs
    where block_timestamp::date BETWEEN '2022-03-14 16:40:20.000' and '2024-01-06 06:52:00.000'
    and contract_address = '0x70c575588b98c1f46b1382c706adaf398a874e3e'
    and topics[0]::string = '0x9b492ca62dd845faf66276dbd14ed82e0d819d8b172cb51433ffe4268ab3dd9e')

    SELECT boundaries.season,
    avg(boundaries.prize) as pool_start,
    sum(ftt.raw_amount/pow(10,18))*0.25 as Banana_CK,
    sum(ftt.raw_amount/pow(10,18)) as Banana_burnt,
    sum(ftt.raw_amount/pow(10,18))*1.25 as Banana_pool,
    avg(boundaries.prize)+(sum(ftt.raw_amount/pow(10,18))*1.25) as final_prize
    FROM polygon.core.fact_token_transfers ftt
    LEFT OUTER JOIN boundaries ON
    ftt.block_timestamp >= boundaries.block_timestamp AND
    ftt.block_timestamp < boundaries.next_timestamp
    where ftt.block_timestamp::date BETWEEN '2022-03-14 16:40:20.000' and '2024-01-06 06:52:00.000'
    AND contract_address = lower('0xbC91347e80886453F3f8bBd6d7aC07C122D87735')
    AND to_address = lower('0x000000000000000000000000000000000000dead')
    group by 1
    order by 1 desc
    Last run: about 1 year ago
    SEASON
    POOL_START
    BANANA_CK
    BANANA_BURNT
    BANANA_POOL
    FINAL_PRIZE
    1
    6510000259.21036.8129611296
    2
    6450001121.644486.565608.210608.2
    3
    63150001963.647854.569818.224818.2
    4
    62100001563.526254.087817.617817.6
    5
    615000989.800253959.2014949.001259949.00125
    6
    6050001156.094624.365780.4510780.45
    7
    5950001755.687022.728778.413778.4
    8
    58150002521.810087.21260927609
    9
    57100001403.245612.967016.217016.2
    10
    5650001368.725474.886843.611843.6
    11
    5515000928.643714.564643.219643.2
    12
    54150001764.647058.568823.223823.2
    13
    5350001666.766667.048333.813333.8
    14
    52200002575.3610301.4412876.832876.8
    15
    51200003778.2815113.1218891.438891.4
    16
    50100007332.829331.23666446664
    17
    49200009188.6836754.7245943.465943.4
    18
    48150007202.5628810.2436012.851012.8
    19
    472000011878.1247512.4859390.679390.6
    20
    46100007289.7229158.8836448.646448.6
    65
    3KB
    100s