logx_tradeMarketdominance
Updated 2023-06-24
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
›
⌄
-- forked from m-zamani-WmWD3E / Top 50 sales by volume @ https://flipsidecrypto.xyz/m-zamani-WmWD3E/q/kGDcKgzZu0ML/top-50-sales-by-volume
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,
s.CONTRACT_ADDRESS,
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.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,
case
when contract_address = '0x17f4baa9d35ee54ffbcb2608e20786473c7aa49f' then 'GMX Blueberry Club'
when contract_address = '0xede855ced3e5a59aaa267abdddb0db21ccfe5072' then 'Diamond Pepes'
when contract_address = '0x4ee9fe9500e7c4fe849add9b14beec5ec5b7d955' then 'Dopex Bridgoor NFT'
when contract_address = '0xfae39ec09730ca0f14262a636d2d7c5539353752' then 'Arbitrum Odyssey NFT'
when contract_address = '0x642ffab2752df3bce97083709f36080fb1482c80' then 'Government Toucans'
Run a query to Download Data