Query editor
WITH wash_trading AS (SELECT distinct wash_trading_filter_ AS wash_trading_filterFROM (SELECT CASE WHEN '{{Wash Trading Filter}}' IN ('ON', 'OFF') THEN false END AS wash_trading_filter_UNIONSELECT CASE WHEN '{{Wash Trading Filter}}'='OFF' THEN true END AS wash_trading_filter_)WHERE wash_trading_filter_ IS NOT NULL), raw_data AS (SELECT time, marketplace, COUNT(distinct trader) AS tradersFROM (SELECT distinct date_trunc('week', t.block_time) AS time, CASE WHEN LENGTH(t.aggregator_name) > 1 THEN LOWER(t.aggregator_name) ELSE LOWER(t.project) END AS marketplace, t.buyer AS traderFROM nft.trades tINNER JOIN nft.wash_trades wt ON wt.block_number=t.block_numberAND wt.unique_trade_id=t.unique_trade_idAND wt.is_wash_trade IN (SELECT wash_trading_filter FROM wash_trading)WHERE t.blockchain = 'ethereum'AND (wt.is_wash_trade IN (SELECT wash_trading_filter FROM wash_trading) OR wt.is_wash_trade IS NULL)AND t.block_time >= date_trunc('week', NOW() - interval '2' year)AND t.block_time < date_trunc('week', NOW())AND t.tx_hash NOT IN (SELECT tx_hash FROM query_1982559) -- https://dune.com/queries/1982559 (Remove ocasional bad data)UNION ALLSELECT distinct date_trunc('week', t.block_time) AS time, CASE WHEN LENGTH(t.aggregator_name) > 1 THEN LOWER(t.aggregator_name) ELSE LOWER(t.project) END AS marketplace, t.seller AS traderFROM nft.trades tINNER JOIN nft.wash_trades wt ON wt.block_number=t.block_numberAND wt.unique_trade_id=t.unique_trade_idAND wt.is_wash_trade IN (SELECT wash_trading_filter FROM wash_trading)WHERE t.blockchain = 'ethereum'AND t.block_time >= date_trunc('week', NOW() - interval '2' year)AND t.block_time < date_trunc('week', NOW())AND t.tx_hash NOT IN (SELECT tx_hash FROM query_1982559) -- https://dune.com/queries/1982559 (Remove ocasional bad data))GROUP BY 1, 2)SELECT rd.time, m.name, rd.tradersFROM raw_data rdINNER JOIN query_1933124 m ON m.codename=rd.marketplace -- https://dune.com/queries/1933124 For proper marketplace name capitalisation