Query editor
WITH trades AS (SELECT evt_block_time, evt_tx_hash, user, contract_address, action, marketId, value, 'abstract' as chainFROM myriad_abstract.predictionmarketv3_evt_marketactiontxWHERE action IN (0,1)UNION ALLSELECT evt_block_time, evt_tx_hash, user, contract_address, action, marketId, value, chainFROM myriad_multichain.predictionmarketv3_4_evt_marketactiontxWHERE action IN (0,1)UNION ALLSELECT evt_block_time, evt_tx_hash, user, contract_address, action, marketId, value, 'abstract' as chainFROM myriad_abstract.predictionmarketv3_3_points_evt_marketactiontxWHERE action IN (0,1)UNION ALLSELECT evt_block_time, evt_tx_hash, user, contract_address, action, marketId, value, 'abstract' as chainFROM myriad_abstract.predictionmarketv4_evt_marketactiontxWHERE action IN (0,1)),markets AS (SELECT * FROM query_5971135),daily AS (SELECTDATE_TRUNC('day', evt_block_time) as "Date",user as "User"FROM trades tRIGHT JOIN markets mON t.contract_address = m.contract_addressAND t.marketId = m.marketId),-- Get first seen date for each useruser_first_seen AS (SELECT"User",MIN("Date") AS first_seen_dateFROM dailyWHERE "User" IS NOT NULL -- Exclude null usersGROUP BY "User"),-- Calculate metrics for ALL dates with activitydaily_metrics AS (SELECTd."Date",COUNT(DISTINCT d."User") AS "DAU", -- Daily Active UsersCOUNT(DISTINCT CASE WHEN d."Date" = ufs.first_seen_date THEN d."User" END) AS "New",COUNT(DISTINCT CASE WHEN d."Date" > ufs.first_seen_date THEN d."User" END) AS "Old"FROM daily dLEFT JOIN user_first_seen ufs ON d."User" = ufs."User"WHERE d."User" IS NOT NULL -- Exclude null usersGROUP BY d."Date")-- Final output with cumulative metricsSELECT"Date","DAU","New",