Flipside TeamMost Traded NFTs
    Updated 2024-11-14
    with prices as (
    select date_trunc(day,hour) as date, avg(PRICE) as token_price
    from near.price.ez_prices_hourly
    where symbol ilike 'wNEAR' and hour::date>='{{starting_date}}'
    group by 1
    ),
    succeeded_txs as (
    select tx_hash as tx
    from near.core.fact_transactions
    where BLOCK_TIMESTAMP::date>='{{starting_date}}'
    and tx_succeeded=true
    group by tx_hash),

    price_uwon as (
    select
    value[0]::string as hour,
    value[1]::float as token_price
    from (select livequery.live.udf_api('https://flipsidecrypto.xyz/api/queries/25a3446f-6edd-4e73-a282-563319ed1940/latest-run')
    as response), lateral FLATTEN (input => response:data:result:rows)),
    ----------------------- nft transfers -----------------------

    fact_nft_transfers as (
    select a.* from (
    select block_id, block_timestamp, tx_hash, fact_receipts_id,
    event_json:data[0]:old_owner_id as from_address,
    event_json:data[0]:new_owner_id as to_address,
    token_contract, value as token_id, 'NFT' as token_type, event as method_name, event_json as log
    from
    (select block_id, block_timestamp, TRY_PARSE_JSON(REPLACE(l.value::STRING, 'EVENT_JSON:', '')) AS event_json,
    event_json:event as event, fact_receipts_id,
    RECEIVER_ID as token_contract, tx_hash, event_json:data[0]:token_ids as token_ids
    from near.core.fact_receipts a,
    LATERAL FLATTEN(INPUT => a.logs) l
    where event_json:standard='nep171'
    and event='nft_transfer') a,
    LATERAL FLATTEN(INPUT => a.token_ids) l) as a
    QueryRunArchived: QueryRun has been archived