Query editor
-- Step 1: Retrieve chain transaction volumesWITH chain_tx_volumes AS (SELECTdate_trunc('{{granularity}}', e.evt_block_time) as date_trade,{{token_address}} as contract_address,"to" as Wallet_address,e.evt_tx_hash as tx_hash,ceiling((CAST(e.value as REAL) / power(10, b.decimals))) as tx_valueFROMerc20_{{chain}}.evt_transfer eJOINtokens.erc20 b ON e.contract_address = b.contract_addressWHEREe.contract_address = {{token_address}}AND evt_block_time > CAST('{{start_date}}' AS timestamp)AND evt_block_time < CAST('{{end_date}}' AS timestamp)),-- Step 2: Calculate Quartilesvolume_statistics AS (SELECTAPPROX_PERCENTILE(tx_value, 0.25) AS q1,APPROX_PERCENTILE(tx_value, 0.50) AS median,APPROX_PERCENTILE(tx_value, 0.75) AS q3,(APPROX_PERCENTILE(tx_value, 0.75) - APPROX_PERCENTILE(tx_value, 0.25)) AS iqrFROMchain_tx_volumes),secondary_thresholds AS (SELECTq1,median,q3,iqr,q1 + 0.25 * iqr AS tier1_upper,q1 + 0.50 * iqr AS tier2_upper,q1 + 0.75 * iqr AS tier3_upperFROMvolume_statistics),classified_transactions AS (SELECTcv.date_trade,cv.contract_address,cv.wallet_address,cv.tx_hash,cv.tx_value,CASEWHEN cv.tx_value < st.q1 THEN '(Low) 🦀'WHEN cv.tx_value >= st.q1 AND cv.tx_value < st.tier1_upper THEN '(Tier 1) 🐟'WHEN cv.tx_value >= st.tier1_upper AND cv.tx_value < st.tier2_upper THEN '(Tier 2) 🐡'WHEN cv.tx_value >= st.tier2_upper AND cv.tx_value < st.tier3_upper THEN '(Tier 3) 🐬'WHEN cv.tx_value >= st.tier3_upper AND cv.tx_value < st.q3 THEN '(Tier 4) 🐳'ELSE '(High) 🐋'END AS volume_tier