Abbas_ra21Layer 2: NFT
Updated 2023-02-04
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
›
⌄
with tb AS (select date_trunc('day',Hour) AS day,
avg(price) AS price from ethereum.core.fact_hourly_token_prices where SYMBOL='WETH'
group by 1),
sales AS (select A.BLOCK_TIMESTAMP,A.TX_HASH,A.EVENT_INPUTS:_to AS buyer,A.EVENT_INPUTS:_from AS seller,A.EVENT_INPUTS:_id AS Token_id,C.EVENT_INPUTS:value/1e18 AS Price,C.EVENT_INPUTS:value/1e18*price AS USD_Price,A.CONTRACT_ADDRESS As NFT_ADDDRESS
from polygon.core.fact_event_logs A
inner join (select * from polygon.core.fact_event_logs
where EVENT_NAME='Transfer' and ORIGIN_TO_ADDRESS='0x00000000006c3852cbef3e08e8df289169ede581') C
on A.EVENT_INPUTS:_to=C.EVENT_INPUTS:from and A.EVENT_INPUTS:_from=C.EVENT_INPUTS:to and A.TX_HASH=C.TX_HASH
inner join (select * from ethereum.core.fact_hourly_token_prices where symbol='WETH') on date_trunc('HOUR',A.BLOCK_TIMESTAMP)=HOUR
where A.EVENT_NAME='TransferSingle' and A.ORIGIN_TO_ADDRESS='0x00000000006c3852cbef3e08e8df289169ede581')
select
'Polygon' AS chain,
DATE_TRUNC('week', BLOCK_TIMESTAMP) AS week,
case when PROJECT_NAME is not null then PROJECT_NAME else NFT_ADDDRESS end AS "Collection",
count(*) AS "sales number",
count(distinct buyer) AS "buyers number",
sum(USD_Price) AS "Volume",
avg(USD_Price) AS "Average Price"
from Sales left join polygon.core.dim_labels on NFT_ADDDRESS=NFT_ADDDRESS where BLOCK_TIMESTAMP::Date >= dateadd('month',-6,CURRENT_DATE)
group by 1,2,3