Query editor
-- This query has been migrated to run on DuneSQL.-- If you need to change back because you’re seeing issues, use the Query History view to restore the previous version.-- If you notice a regression, please email [email protected].WITHopensea_trades /* get all OpenSea trades on Ethereum */ AS (SELECTt.block_time,DATE_TRUNC('day', t.block_time) AS day,DATE_TRUNC('week', t.block_time) AS week,DATE_TRUNC('month', t.block_time) AS month,t.buyer,t.seller,t.nft_contract_address,t.token_id,CASEWHEN t.currency_symbol IN ('ETH', 'WETH') THEN t.amount_original /* for trades in ETH/WETH, use existing amount */ELSE ROUND(t.amount_usd, 10) / CAST(p_eth.price /* for other ERC20 tokens, convert USD value back to ETH */ AS DOUBLE)END AS amount_eth,amount_usd,tx_hashFROMopensea_ethereum.trades AS tLEFT JOIN prices.usd AS p_eth ON DATE_TRUNC('minute', t.block_time) = p_eth.minuteAND p_eth.blockchain = 'ethereum'AND p_eth.symbol = 'WETH'WHERETRUEAND t.buyer <> t.seller),output AS (SELECTmonth,COUNT(DISTINCT CONCAT(CAST(nft_contract_address AS VARCHAR),CAST('-' AS VARCHAR),CAST(token_id AS VARCHAR))) AS num_nfts,COUNT(DISTINCT tx_hash) AS num_txnsFROMopensea_tradesGROUP BY1)SELECT*FROMoutput