Flipside Teamnear nft - 01-01 over time
    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
    Last run: about 2 months ago
    DATE
    Sales
    Collections
    NFTs
    Volume
    Average Price
    Median Price
    Sellers
    Buyers
    Total Sales
    Total Volume
    1
    2023-06-05 00:00:00.000421633632.58267799815.0614923330.595216689261921972922940717.3328905
    2
    2023-03-03 00:00:00.0004223317313372.27595595331.68785771631.5327633931528920641922713309.9785093
    3
    2022-01-12 00:00:00.000247620532351293048.421485476118.35558218343.0452482838771109122661642040.03326408
    4
    2024-11-30 00:00:00.000307242434111.90745337613.3938353535.75427511914051249427299805.9179953
    5
    2023-04-19 00:00:00.00011539902758.87466907623.99021451430.713393179512721382522871996.7412309
    6
    2024-03-29 00:00:00.000417284006406.36537065715.36298651.9525386859011026361623548999.8477916
    7
    2023-05-24 00:00:00.00016127652064.10940178912.82055529112.945493169402821834722925592.3104926
    8
    2024-09-07 00:00:00.000234252061533.0300216396.5514103494.552964310810547600326956487.9441844
    9
    2022-09-17 00:00:00.0002101352077301.20242994834.7676306193.8023642251329617900222070711.6736694
    10
    2023-10-17 00:00:00.000123121.0151992720.084599939370.010159123243322590723017575.049804
    11
    2024-07-22 00:00:00.000166814123120951.43734598812.56081375710.47284650545486426704678.2230155
    12
    2023-01-24 00:00:00.0008540702560.51541254330.1237107367.051077654524220143322608853.3247172
    13
    2024-11-23 00:00:00.000311302543440.72991061411.0634402275.2740512211550992527268090.443158
    14
    2022-10-09 00:00:00.000122791205231.75243130242.883216658.788411831867318227422192231.5554145
    15
    2023-11-28 00:00:00.00019719160.7265027018.4592896161.73958574911722677823038236.8422504
    16
    2022-02-24 00:00:00.00013996521376110023.82243519178.64461932526.5336917527107727418310004693.8929127
    17
    2024-08-15 00:00:00.000277372574715.7320087617.0243032815.90452511312846821526874651.6404566
    18
    2022-01-08 00:00:00.000136110271328252740.060098788185.70173409260.4858913085614893121506209.525057202
    19
    2023-12-30 00:00:00.0008020483894.35680089348.67946001111.028938333332323007023225470.1150654
    20
    2023-12-28 00:00:00.0007735753888.21099338650.49624666720.476679317394322995223218668.1944817
    ...
    1091
    116KB
    544s