Query editor
WITH new_users AS (SELECTcohort_date AS month,recipient_chain,COUNT(user_address) AS new_users_countFROM (SELECT"from" AS user_address,recipient_chain,MIN(DATE_TRUNC('month', block_time)) AS cohort_dateFROMdune.serotonin_data.result_wormhole_datasetGROUP BY "from", recipient_chain)GROUP BY cohort_date, recipient_chain),monthly_metrics AS (SELECTDATE_TRUNC('month', block_time) AS month,recipient_chain,COUNT(DISTINCT "from") AS mau,COUNT(DISTINCT tx_hash) AS tx_count,SUM(amount_usd) AS vol_usdFROMdune.serotonin_data.result_wormhole_datasetGROUP BY DATE_TRUNC('month', block_time),recipient_chain)SELECTmetrics.month,metrics.recipient_chain,mau,new_users_count,tx_count,vol_usd,TRY_CAST(new_users_count AS DECIMAL(38,10))/TRY_CAST(mau AS DECIMAL(38,10)) AS new_users_pct,SUM(new_users_count) OVER(PARTITION BY metrics.recipient_chain ORDER BY metrics.month) AS total_users,SUM(tx_count) OVER(PARTITION BY metrics.recipient_chain ORDER BY metrics.month) AS total_txs,SUM(vol_usd) OVER(PARTITION BY metrics.recipient_chain ORDER BY metrics.month) AS total_usdFROMmonthly_metrics metricsLEFT JOIN new_users new ON metrics.month = new.month AND new.recipient_chain = metrics.recipient_chainORDER BY metrics.month DESC, tx_count DESC