MLDZMNRetro2
    Updated 2022-12-21
    with t1 as (select
    HOUR::date as day,
    avg(price) as ETH_price
    from ethereum.core.fact_hourly_token_prices
    where symbol='WETH'
    group by 1)

    select
    PROJECT_NAME,
    count(distinct tx_hash) as no_mints,
    count(distinct NFT_TO_ADDRESS) as no_minter,
    count(distinct TOKENID) as no_nfts,
    sum(MINT_PRICE_ETH) as volume_eth,
    sum(MINT_PRICE_USD) as volume_usd,
    sum(tx_fee) as fee_eth,
    avg(tx_fee) as avg_fee_eth,
    sum(tx_fee*ETH_price) as fee_usd,
    avg(tx_fee*ETH_price) as avg_fee_usd,
    row_number()over(order by fee_eth desc) as rank1
    from ethereum.core.ez_nft_mints s left join t1 a on s.BLOCK_TIMESTAMP::date=a.day
    where BLOCK_TIMESTAMP>='2022-01-01'
    group by 1 having PROJECT_NAME is not null
    order by 7 desc limit 20
    Run a query to Download Data