shreexUntitled Query
Updated 2022-07-28
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
with transactions as (select
tx:receipt[6]:outcome:logs[0] as tx_log,
tx:actions[0]:FunctionCall:deposit/pow(10,24) as salee,
tx_hash,
block_timestamp
from near.core.fact_transactions where (tx:receipt[6]:outcome:executor_id='marketplace.paras.near' or tx:receipt[0]:outcome:executor_id='x.paras.near') ),
sales as (
select
block_timestamp,
salee,
tx_hash,
TRY_PARSE_JSON(tx_log) as json_parse,
json_parse:params:price/pow(10,24) as sale_price,
json_parse:params:nft_contract_id as nft_contract,
json_parse:type as sale_type,
json_parse:params:buyer_id as buyer,
json_parse:params:owner_id as seller,
json_parse:params:is_offer as offer
from transactions)
select
date_trunc('day',block_timestamp) as date,
sum(sale_price) as sales_volume,
sum(salee) as sales_volume2,
sales_volume+sales_volume2 as daily_sales_volume,
count(distinct nft_contract) as unique_nft_contracts,
count(distinct buyer) as unique_sellers,
count(distinct seller) as unique_buyers,
count(distinct tx_hash) as daily_sales
--sum(unique_sellers) over (order by date) as unique_sellers_growth,
-- sum(unique_buyers) over (order by date) as unique_buyers_growth,
--sum(unique_nft_contracts) over (order by date) as nft_contracts_growth,