Query editor
WITH base AS (SELECTDATE(ts_source) AS block_date,source_chain,destination_chain,COALESCE(usd_value, 0) AS usd_value,tx_hash_source,addr_fromFROM layerzero.messagesWHEREts_source >= TIMESTAMP '2025-07-01'AND (source_chain = 'Etherlink'OR destination_chain = 'Etherlink')),directional AS (SELECTblock_date,CASEWHEN source_chain = 'Etherlink' THEN destination_chainELSE source_chainEND AS other_chain,source_chain,destination_chain,usd_value,tx_hash_source,addr_fromFROM base)SELECTblock_date,other_chain,-- volume INTO Etherlink (other → Etherlink)SUM(CASE WHEN destination_chain = 'Etherlink' THEN usd_value ELSE 0 END)AS volume_in_etherlink_usd,-- volume OUT OF Etherlink (Etherlink → other)SUM(CASE WHEN source_chain = 'Etherlink' THEN usd_value ELSE 0 END)AS volume_out_etherlink_usd,-- total + netSUM(usd_value) AS total_volume_usd,SUM(CASE WHEN destination_chain = 'Etherlink' THEN usd_value ELSE 0 END)- SUM(CASE WHEN source_chain = 'Etherlink' THEN usd_value ELSE 0 END)AS net_volume_etherlink_usd,-- tx countsCOUNT(CASE WHEN destination_chain = 'Etherlink' THEN 1 END)AS tx_count_in_etherlink,COUNT(CASE WHEN source_chain = 'Etherlink' THEN 1 END)AS tx_count_out_etherlink,COUNT(*) AS total_tx_count,-- distinct txs and usersAPPROX_DISTINCT(tx_hash_source) AS distinct_messages,APPROX_DISTINCT(addr_from) AS unique_users