Abbas_ra21Layer 2: NFT
    Updated 2023-02-04
    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