ddccnft_vols
Updated 2022-11-27
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
›
⌄
with NFT_tx as (select tx_id, MAX(contract_address) as nft_address
from ethereum.udm_events t0
where block_timestamp::date > '2022-01-01'
and origin_function_name = 'atomicMatch_'
and event_type = 'erc20_transfer'
and contract_address in (select contract_address FROM ethereum.nft_events GROUP BY 1)
GROUP BY 1
),
TRANSFERTS as
( select block_timestamp, tx_id, symbol, amount
from ethereum.udm_events as event_tab
where block_timestamp::date > '2022-01-01'
and tx_id in (select tx_id
from ethereum.udm_events
where block_timestamp::date > '2022-01-01'
and origin_function_name = 'atomicMatch_'
and tx_id is not NULL
and contract_address in (select contract_address FROM ethereum.nft_events GROUP BY 1)
GROUP BY 1
)
and to_label_subtype = 'marketplace'
and symbol = 'ETH')
SELECT date_trunc('week', block_timestamp) AS time_bucket, nft_tab.nft_address, sum(amount), MEDIAN(amount), avg(amount), count(T.tx_id)
FROM TRANSFERTS as T
LEFT JOIN NFT_tx as nft_tab ON T.tx_id = nft_tab.tx_id
GROUP BY 1,2
ORDER BY 2, 1 DESC