Query editor
WITH trades AS (SELECT tx_from,DATE_TRUNC('month', block_time) AS activity_month,amount_usd,'trade' AS activity_typeFROM dex.tradesWHERE 1=1AND blockchain = 'hyperevm'AND project = 'prjx'),pools AS (SELECT VARBINARY_LTRIM(BYTEARRAY_SUBSTRING(data, 33, 32)) AS poolFROM hyperevm.logsWHERE 1=1AND contract_address = 0xFf7B3e8C00e57ea31477c32A5B52a58Eea47b072AND topic0 = 0x783cca1c0412dd0d695e784568c96da2e9c22ff989357a2e8b1d9b2b4e6b7118),mints AS(SELECT tx_from,DATE_TRUNC('month', block_time) AS activity_month,0 AS amount_usd,'mint' AS activity_typeFROM hyperevm.logsWHERE 1=1AND contract_address IN (SELECT pool FROM pools)AND topic0 = 0x7a53080ba414158be7ec69b987b5fb7d07dee101fe85488f0853ae16239d0bde),combined AS (SELECT *FROM tradesUNION ALLSELECT *FROM mints),first_action AS (SELECT tx_from,MIN(activity_month) AS first_monthFROM combinedGROUP BY tx_from),user_type AS(SELECT c.activity_month,c.tx_from AS users,c.activity_type,CASE WHEN c.activity_month = f.first_month THEN 'New Users'ELSE 'Returning Users' END AS user_type,c.amount_usd AS volumeFROM combined cLEFT JOIN first_action f ON f.tx_from = c.tx_from)SELECT *FROM (SELECT activity_month,COUNT(DISTINCT users) OVER (PARTITION BY activity_month) AS monthly_users,user_type,