Query editor
with collections AS (SELECTcontract,MAX(name) AS name,MIN(created_at) AS creation_time,SUM(floor_ask_value)/POWER(10, 18) AS floorFROM reservoir.collectionsGROUP BY contract),top1000_contracts AS (-- CTE to get the top 1000 contracts in terms of-- traded value. Helps with selecting collections-- with a reliable floor price.SELECTnft_contract_address,any_value(collection) as collection,any_value(c.name) as label,count(*) as n_transfers,sum(amount_original) as amount_crypto,sum(amount_usd) as amount_usd,sum(amount_original * 1500) as amount_usd_normalizedFROM (select * from nft.trades) nftsleft join collections c on c.contract = nfts.nft_contract_addresswhere trueand blockchain = 'ethereum'and currency_symbol IN ('ETH','WETH') -- todo: add conversion with other currenciesgroup by 1 order by amount_usd_normalized desclimit 1000),eth_prices AS (select *from prices.usdwhere trueand blockchain is null -- ethereumand symbol = 'ETH'),floor_prices AS (-- CTE to get the weekly floor price of nft collections.selectdate_trunc('week', block_time) AS date,nft_contract_address,count(*) as n_trades,approx_percentile(amount_original, 0.1) as floor_price_originalfrom nft.tradesinner join top1000_contracts USING(nft_contract_address) -- inner join with top1000_contracts for performancewhere true-- todo: potentially remove wash trades from floor pricesand (token_id <> cast(1935 as uint256) or nft_contract_address <> 0xfb3765e0e7ac73e736566af913fa58c3cfd686b7) -- weird trades of tokenand currency_symbol IN ('WETH', 'ETH') -- todo: support all changesgroup by 1, 2),transactions_items AS (