Query editor
WITH recent_txns AS (SELECT"to" AS contract_address,gas_used,gas_price,COALESCE(fee_currency, 0x471ece3750da237f93b8e339c536989b8978a438) AS fee_currency,(CAST(gas_used AS DOUBLE) * CAST(gas_price AS DOUBLE) / 1e18) AS gas_fee_token,block_time,DATE_TRUNC('hour', block_time) AS block_hour,-- native transfer flag: empty calldata + actual CELO movedCASE WHEN (data IS NULL OR data = 0x) AND value > UINT256 '0'THEN true ELSE false END AS is_native_transfer,CASE WHEN "to" IN (SELECT address FROM query_7820079)THEN true ELSE false END AS is_updownFROM celo.transactionsWHERE block_time >= NOW() - INTERVAL '{{days}}' DAY),price_mapping AS (SELECTrt.*,CASEWHEN rt.fee_currency = 0x2f25deb3848c207fc8e0c34035b3ba7fc157602b THEN 0xcebA9300f2b948710d2653dD7B07f33A8B32118C -- USDC adapterWHEN rt.fee_currency = 0x0e2a3e05bc9a16f5292a6170456a710cb89c6f72 THEN 0x48065fbBE25f71C9282ddf5e1cD6D6A887483D5e -- USDT adapterELSE rt.fee_currencyEND AS price_lookup_addressFROM recent_txns rt),txns_with_price AS (SELECTpm.*,p.price AS fee_token_price_usdFROM price_mapping pmLEFT JOIN prices.hour pON p.blockchain = 'celo'AND p.contract_address = pm.price_lookup_addressAND p.timestamp = pm.block_hour),fees_per_currency AS (SELECT-- collapse all native transfers under one synthetic address; otherwise keep the real "to"CASEWHEN is_native_transferTHEN 0x0000000000000000000000000000000000000000WHEN is_updownTHEN 0x0000000000000000000000000000000000000001ELSE contract_addressEND AS contract_address,is_native_transfer,is_updown,fee_currency,COUNT(*) AS tx_count,SUM(gas_used) AS total_gas_used,SUM(gas_fee_token) AS total_fees_token,SUM(gas_fee_token * fee_token_price_usd) AS total_fees_usdFROM txns_with_priceGROUP BYCASEWHEN is_native_transfer