alitaslimiRetention Cohort
    Updated 2024-11-25
    -- Inspired by 0xDataWolf's query
    -- https://flipsidecrypto.xyz/0xDataWolf/q/jEC6M61jinzx/time-based-cohort-wide

    with
    -------------------- Hourly AVAX Price --------------------
    prices as (
    select
    hour,
    price as avax_price
    from
    avalanche.price.ez_prices_hourly
    where
    token_address = '0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7'
    ),
    -------------------- Raw Sale Transactions --------------------
    transactions as (
    select
    block_timestamp,
    tx_hash,
    'Hyperspace' as marketplace,
    case decoded_log:direction
    when 0 then decoded_log:maker
    when 1 then decoded_log:taker
    end as seller_address,
    case decoded_log:direction
    when 0 then decoded_log:taker
    when 1 then decoded_log:maker
    end as buyer_address,
    decoded_log:erc721Token as nft_address,
    decoded_log:erc721TokenId as tokenid,
    decoded_log:erc20TokenAmount / pow(10, 18) as price,
    price * avax_price as price_usd
    from
    avalanche.core.ez_decoded_event_logs logs
    join prices on date_trunc('hour', logs.block_timestamp) = prices.hour
    where