MLDZMNq1.1
    Updated 2024-02-18
    -- forked from q1 @ https://flipsidecrypto.xyz/edit/queries/7df861f2-b2b0-41dd-9a12-c81b32f2d128

    WITH tb1 AS (
    SELECT *
    FROM ethereum.core.fact_event_logs
    WHERE(TOPICS[0] = '0xf30861289185032f511ff94a8127e470f3d0e6230be4925cb6fad33f3436dffb' OR
    TOPICS[0] = '0xf1d95ed4d1680e6f665104f19c296ae52c1f64cd8114e84d55dc6349dbdafea3')
    AND block_number >= 17671128
    and TX_STATUS='SUCCESS'
    ),

    price_tab as (select
    HOUR::date as day,
    avg(price) as price1
    from ethereum.price.ez_hourly_token_prices
    where SYMBOL = 'WETH'
    group by 1),

    final as (select
    s.BLOCK_NUMBER,
    s.BLOCK_TIMESTAMP,
    s.TX_HASH,
    s.contract_address,
    CASE
    WHEN s.TOPICS[0] = '0xf30861289185032f511ff94a8127e470f3d0e6230be4925cb6fad33f3436dffb' THEN s.contract_address
    WHEN s.TOPICS[0] = '0xf1d95ed4d1680e6f665104f19c296ae52c1f64cd8114e84d55dc6349dbdafea3' THEN concat('0x',substring(s.TOPICS[1], 27, 40))
    END AS SELLER_ADDRESS,
    CASE
    WHEN s.TOPICS[0] = '0xf30861289185032f511ff94a8127e470f3d0e6230be4925cb6fad33f3436dffb' THEN concat('0x',substring(s.TOPICS[1], 27, 40))
    WHEN s.TOPICS[0] = '0xf1d95ed4d1680e6f665104f19c296ae52c1f64cd8114e84d55dc6349dbdafea3' THEN concat('0x',substring(s.TOPICS[2], 27, 40))
    END AS BUYER_ADDRESS,
    CASE
    WHEN s.TOPICS[0] = '0xf30861289185032f511ff94a8127e470f3d0e6230be4925cb6fad33f3436dffb' THEN s.TOPICS[2]
    WHEN s.TOPICS[0] = '0xf1d95ed4d1680e6f665104f19c296ae52c1f64cd8114e84d55dc6349dbdafea3' THEN s.TOPICS[3]
    END AS ethscription_id,
    a.ETH_VALUE as price_eth,
    Last run: about 1 year ago
    DATE
    SALE_NO
    BUYER_NO
    SELLER_NO
    NO_TOKENS
    NO_MARKETPLACES
    CUMULATIVE_SALE
    1
    2023-07-10 00:00:00.0004323238243
    2
    2023-07-17 00:00:00.0001614414459
    3
    2023-07-24 00:00:00.0002300483551225752359
    4
    2023-07-31 00:00:00.000182382041266216929720597
    5
    2023-08-07 00:00:00.000561883811725478526215
    6
    2023-08-14 00:00:00.00030245026952898329239
    7
    2023-08-21 00:00:00.00021353565712078331374
    8
    2023-08-28 00:00:00.00021462855552116533520
    9
    2023-09-04 00:00:00.00030004926962918336520
    10
    2023-09-11 00:00:00.00017773374901760338297
    11
    2023-09-18 00:00:00.00012602103731238339557
    12
    2023-09-25 00:00:00.00012131942871207240770
    13
    2023-10-02 00:00:00.000593264311605737446702
    14
    2023-10-09 00:00:00.000875796113258542355459
    15
    2023-10-16 00:00:00.00012278981208011901367737
    16
    2023-10-23 00:00:00.000140951280211313832281832
    17
    2023-10-30 00:00:00.0002051116612229182124102343
    18
    2023-11-06 00:00:00.0001930520922555158373121648
    19
    2023-11-13 00:00:00.0001646816042213134233138116
    20
    2023-11-20 00:00:00.000442858494139133142544
    32
    2KB
    90s