MLDZMNgmx2
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 p_eth as (select
HOUR::date as day,
avg(price) as token_price
from ethereum.core.fact_hourly_token_prices
where SYMBOL = 'WETH'
group by 1
),
decoded_sale as (select
s.BLOCK_TIMESTAMP,
s.tx_hash,
DECODED_LOG:from as seller,
DECODED_LOG:to as buyer,
DECODED_LOG:tokenId as TOKENID,
ETH_value as amount,
ETH_value*token_price as amount_usd
from arbitrum.core.fact_decoded_event_logs s
left join arbitrum.core.fact_transactions a on s.tx_hash=a.tx_hash
left join p_eth p on s.BLOCK_TIMESTAMP::date=p.day
where s.CONTRACT_ADDRESS = '0x17f4baa9d35ee54ffbcb2608e20786473c7aa49f'
and s.EVENT_NAME = 'Transfer'
and DECODED_LOG:from != '0x0000000000000000000000000000000000000000'
and ETH_value>0.001
and a.STATUS='SUCCESS'
)
select
date_trunc('week',BLOCK_TIMESTAMP) as date,
count(distinct tx_hash) as sale_no,
count(distinct buyer) as buyer_no,
count(distinct seller) as seller_no,
sum(amount) as volume_ETH,
sum(amount_usd) as volume_usd,
avg(amount_usd) as avg_sale_usd,
median(amount_usd) as med_price_usd,
min(amount_usd) as min_price_usd,
Run a query to Download Data