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 date_trunc('day', t.block_time) AS time, CASE WHEN LENGTH(t.aggregator_name) > 1 THEN LOWER(t.aggregator_name) ELSE LOWER(t.project) END AS marketplace, COUNT(distinct t.unique_trade_id) AS total_tradesFROM 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('day', NOW() - interval '3' month)AND t.block_time < date_trunc('day', NOW())AND t.tx_hash NOT IN (SELECT tx_hash FROM query_1982559) -- https://dune.com/queries/1982559 (Remove occasional bad data)GROUP BY 1, 2), ordering AS (SELECT ROW_NUMBER() OVER (ORDER BY total_trades DESC) AS ranking, marketplace, total_tradesFROM raw_dataWHERE time = (SELECT MAX(time) FROM raw_data))SELECT rd.time, COALESCE(names.name, codename) AS name, rd.total_tradesFROM raw_data rdLEFT JOIN query_1933124 names ON names.codename=rd.marketplace -- https://dune.com/queries/1933124 For proper marketplace name capitalisationsLEFT JOIN ordering o ON o.marketplace=rd.marketplaceORDER BY o.ranking