ramishoowUntitled Query
    Updated 2023-07-04
    with ramishoow AS ( SELECT blocK_timestamp, tx_hash, tx:receipt as receipt, tx:public_key as public_key, tx:signer_id as signer_id, tx:receiver_id as receiver_id
    FROM near.core.fact_transactions where block_timestamp::date < CURRENT_DATE and block_timestamp>= current_date - 90 ), JSON_PARSING AS ( SELECT block_timestamp,
    tx_hash, public_key, signer_id, receiver_id, seq,key,path,index, replace(value:outcome:logs[0], '\\') as logs, check_json(logs) as checks FROM ramishoow,
    table(flatten(input => receipt)) ), nft_tx_log AS ( SELECT block_timestamp, tx_hash, public_key, signer_id, receiver_id, try_parse_json(logs) as parse_logs, parse_logs:type as type,
    parse_logs:params:buyer_id as buyer_id, parse_logs:params:owner_id as owner_id, parse_logs:params:is_offer as is_offer, parse_logs:params:is_auction as is_auction, parse_logs:params:nft_contract_id as nft_contract_id,
    --parse_logs:params:buyer_id as buyer_id, parse_logs:params:owner_id as owner_id, parse_logs:params:is_offer as is_offer, parse_logs:params:is_auction as is_auction, parse_logs:params:nft_contract_id as nft_contract_id,
    parse_logs:params:token_id as token_id, parse_logs:params:ft_token_id as ft_token_id, parse_logs:params:price/pow(10,24) as near FROM JSON_PARSING where checks is null and logs is not null AND type is not null )
    SELECT date_trunc('day', block_timestamp) as time, count(distinct tx_hash) as transaction_count, sum(near) as near_volume, count(distinct owner_id) as daily_sellers, count(distinct buyer_id) as daily_buyers,
    avg(near) as average_price, near_volume/daily_sellers as average_amount_per_seller, near_volume/daily_buyers as average_amount_per_buyer FROM nft_tx_log where type = 'resolve_purchase' group by time order by time
    Run a query to Download Data