Flipside Teamnear nft - 01-02 market 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
    MARKETPLACE
    Sales
    Collections
    NFTs
    Volume
    Average Price
    Median Price
    Sellers
    Buyers
    Total Sales
    Total Volume
    1
    2023-09-01 00:00:00.000FewAndFar1081122270.86929684921.0265675637.4562969124122491823002869.5353645
    2
    2022-07-09 00:00:00.000Apollo42545223.33996933144.66799386651.1745705274416389621412908.6392291
    3
    2024-07-13 00:00:00.000UWON App717217.64222424331.0917463222.8465069097645129626657823.9342998
    4
    2022-10-31 00:00:00.000Paras135751354103.21800393530.3942074379.243446685808018553022292460.3760709
    5
    2023-09-21 00:00:00.000Paras13613123.1684833569.4744987210.13403033310622537223010072.7512824
    6
    2024-07-23 00:00:00.000Mitte54664275323.2021238469.7494544399.69645621717845544126710922.6406576
    7
    2022-09-25 00:00:00.000Paras145651452996.6624504720.66663758913.889445546898218024722108730.3597901
    8
    2023-05-21 00:00:00.000Mintbase2226.4688281623.2344140813.2344140812221796222919445.3698177
    9
    2024-01-24 00:00:00.000Paras791678769.3323530129.7383842154.938000986381223125023265921.877884
    10
    2024-04-27 00:00:00.000TradePort22321840.80793585138.2185425391.688174563171631697324569944.1967618
    11
    2023-03-26 00:00:00.000L2E65612.5276843292.0879473880.96186728413221019122803709.2925453
    12
    2024-02-18 00:00:00.000Paras5443541244.60305981923.0482048110.7051643262332423311923345955.2010985
    13
    2023-02-09 00:00:00.000Paras7830773668.77487559947.03557532819.022576419464620378222660138.6981932
    14
    2022-09-13 00:00:00.000FewAndFar537191453.01626660827.41540125732.03729335316517814222043828.4314226
    15
    2024-12-23 00:00:00.000Mitte2334920419741.9377778834.1739236412.8619542123152029827374975.2077796
    16
    2024-11-01 00:00:00.000TradePort888531.917366.489662512.399657849572727112352.808211
    17
    2024-12-03 00:00:00.000Mitte36582842578.4998163577.0643830591.64872512911651352727309084.9157033
    18
    2024-12-06 00:00:00.000Mitte28682572318.1338889718.1053632484.590210010951454627322658.5617554
    19
    2024-08-04 00:00:00.000Paras4121411578.86621738.50893212211.826213246488626833804.8453209
    20
    2022-07-14 00:00:00.000Paras2001352003908.58663711819.5429331869.74477404111812516497521445656.9820894
    ...
    3935
    428KB
    544s