Query editor
WITH s AS (SELECTtx_id,pool_name,from_asset,to_asset,from_amount_usd,COALESCE(liq_fee_rune_usd, 0) + COALESCE(liq_fee_asset_usd, 0) AS fee_usdFROM thorchain.defi_swapsWHERE block_timestamp >= CURRENT_DATE - INTERVAL '7' dayAND from_amount_usd > 0),flagged AS (SELECTtx_id,CASEWHEN from_asset LIKE 'THOR.RUNE%' OR to_asset LIKE 'THOR.RUNE%'OR from_asset = 'THOR.RUNE' OR to_asset = 'THOR.RUNE' THEN 'rune_on_a_side'ELSE 'no_rune'END AS rune_flag,pool_nameFROM s)SELECT 'A. RUNE en un lado del leg' AS check_name,rune_flag AS bucket,COUNT(*) AS rows_7d,CAST(NULL AS bigint) AS extraFROM flaggedGROUP BY 2UNION ALLSELECT 'B. legs por tx_id' AS check_name,CAST(legs AS varchar) AS bucket,COUNT(*) AS rows_7d,SUM(legs) AS extraFROM (SELECT tx_id, COUNT(*) AS legs FROM s GROUP BY tx_id)GROUP BY 2UNION ALLSELECT 'C. assets mas frecuentes en from_asset' AS check_name,from_asset AS bucket,COUNT(*) AS rows_7d,CAST(NULL AS bigint) AS extraFROM sGROUP BY 2ORDER BY check_name, rows_7d DESC