with from_address as (sELECT VALUE as source_chain, created_at, id, amount as amount_column, FROM axelar.axelscan.fact_gmp, LATERAL FLATTEN(PARSE_JSON(call)) flattened_data where path = 'chain' ) , sender as (SELECT a.created_at, a.id, b.source_chain, max(amount_column) as amt_column, max(value:"amount") as amount, max(value:"destinationChain") as destination_chain, max(value:"symbol") as symbol, max(value:"from") as sender FROM axelar.axelscan.fact_gmp a join from_address b on a.id = b.id, LATERAL FLATTEN(PARSE_JSON(call)) flattened_data group by all ) , fee as (SELECT a.id, a.created_at as date_ii, max(value:"decimals") as decimal, max(value:"name") as name, max(value:"symbol") as symbol_II, max(value:"value") as amount_II, max(value:"token_price") as token_price FROM axelar.axelscan.fact_gmp a join from_address b on a.id = b.id, LATERAL FLATTEN(PARSE_JSON(FEES)) flattened_data where path = 'source_token' --source_token group by all) , final as (select a.created_at, case when symbol ilike '%usdc%' then 'USDC' when symbol ilike '%eth%' then 'ETH' when symbol ilike '%btc%' then 'BTC' when symbol ilike '%usd%' then 'USDC' when symbol ilike '%dot%' then 'DOT' when symbol ilike '%matic%' then 'MATIC' when symbol ilike '%shib-wei%' then 'SHIB' when symbol ilike '%uatom%' then 'ATOM' when symbol ilike '%pepe%' then 'PEPE' when symbol ilike '%avax%' then 'AVAX' when symbol ilike '%stern%' then 'STERN' when symbol ilike '%aave%' then 'AAVE' when symbol ilike '%glmr%' then 'GLMR' when symbol ilike '%vext%' then 'VEXT' when symbol ilike '%uosmo%' then 'OSMO' when symbol ilike '%dai%' then 'DAI' when symbol ilike '%doge%' then 'DOGE' when symbol ilike '%bnb%' then 'BNB' when symbol ilike '%op-wei%' then 'OP' when symbol ilike '%euro%' then 'EUROC' when symbol ilike '%uluna%' then 'LUNA' when symbol ilike '%uscrt%' then 'SCRT' when symbol ilike '%arb-wei%' then 'ARB' when symbol ilike '%fil-wei%' then 'FIL' when symbol ilike '%uni-wei%' then 'UNI' when symbol ilike '%mkr-wei%' then 'MKR' when symbol ilike '%ukuji%' then 'KUJI' when symbol ilike '%arx-wei%' then 'ARX' when symbol ilike '%sonne-wei%' then 'SONNE' when symbol ilike '%lqdr-wei%' then 'LQDR' when symbol ilike '%rai-wei%' then 'RAI' when symbol ilike '%mpx-wei%' then 'MPX' when symbol ilike '%vela-wei%' then 'VELA' when symbol ilike '%utia%' then 'TIA' when symbol ilike '%ustrd%' then 'STRD' when symbol ilike '%utori%' then 'TORI' when symbol ilike '%ktc-wei%' then 'KTC' when symbol ilike '%knc-wei%' then 'KNC' when symbol ilike '%Lqdr%' then 'LQDR' when symbol ilike '%WFIL%' then 'FIL' when symbol ilike '%hzn-wei%' then 'HZN' when symbol ilike '%ring-wei%' then 'RING' when symbol ilike '%ox-wei%' then 'OX' when symbol ilike '%oath-wei%' then 'OATH' when symbol ilike '%ern-wei%' then 'ERN' when symbol ilike '%gdx-wei%' then 'GDX' when symbol ilike '%usomm%' then 'SOMM' when symbol ilike '%mee%' then 'MEE' when symbol ilike '%uumee%' then 'UMEE' when symbol ilike '%orbs-wei%' then 'ORBS' when symbol ilike '%axlARB%' then 'ARB' when symbol ilike '%axlLqdr%' then 'LQDR' when symbol ilike '%axlOP%' then 'OP' when symbol ilike '%WMAI%' then 'MAI' when symbol ilike '%metal-wei%' then 'METAL' when symbol ilike '%uhuahua%' then 'HUAHUA' when symbol ilike '%islm%' then 'ISLM' when symbol ilike '%milkTIA%' then 'TIA' when symbol ilike '%axlSHIB%' then 'SHIB' when symbol ilike '%axlUNI%' then 'UNI' when symbol ilike '%axlSOMM%' then 'SOMM' when symbol ilike '%axlEUROC%' then 'EUROC' when symbol ilike '%axlFRAX%' then 'FRAX' when symbol ilike '%axlATOM%' then 'ATOM' when symbol ilike '%axlMKR%' then 'MKR' when symbol ilike '%deus-wei%' then 'DEUS' when symbol ilike '%erc20/0x15C3Eb3B621d1Bff62CbA1c9536B7c1AE9149b57%' then 'USDC' when symbol ilike '%link%' then 'LINK' when symbol ilike '%ibc/FE98AAD68F02F03565E9FA39A5E627946699B2B07115889ED812D8BA639576A9%' then 'USDC' when symbol ilike '%factory/sei10hud5e5er4aul2l7sp2u9qp2lag5u4xf8mvyx38cnjvqhlgsrcls5qn5ke/seilor%' then 'SEILOR' when symbol ilike '%factory/osmo1f5vfcph2dvfeqcqkhetwv75fda69z7e5c2dldm3kvgj23crkv6wqcn47a0/umilkTIA%' then 'TIA' when symbol ilike '%pepe%' then 'PEPE' when symbol ilike '%axlSAGA%' then 'SAGA' when symbol ilike '%statom%' then 'ATOM' when symbol ilike '%ibc/498A0751C798A0D9A389AA3691123DADA57DAA4FE165D5C75894505B876BA6E4%' then 'USDC' when symbol ilike '%ftm%' then 'FTM' when symbol ilike '%axlTIA%' then 'TIA' when symbol ilike '%axlDEUS%' then 'DEUS' when symbol ilike '%axlWMAI%' then 'MAI' when symbol ilike '%axlKNC%' then 'KNC' when symbol ilike '%axlFIL%' then 'FIL' when symbol ilike '%axlSTARS%' then 'STARS' when symbol ilike '%axlYieldETH%' then 'ETH' when symbol ilike '%stTIA%' then 'TIA' when symbol ilike '%axlBAL%' then 'BAL' when symbol = 'AXL' then 'AXL' when symbol ilike '%factory/osmo1mlng7pz4pnyxtpq0akfwall37czyk9lukaucsrn30ameplhhshtqdvfm5c/ulvn%' then 'LVN' when symbol ilike '%frax%' then 'FRAX' else symbol end as token_name, a.id, source_chain, destination_chain, symbol, symbol_ii, token_price:"usd" as price, amount, amount_ii, decimal, name, sender, amt_column, amount/pow(10,decimal) as amt, amount_ii/pow(10,decimal) as amt_ii, amt*price as amt_usd, amt_ii*price as amt_usd_ii from sender a join fee b on a.id = b.id ) , price as ( select hour::date as day, symbol, avg(price) as avg_price from crosschain.price.ez_prices_hourly group by 1,2) , final_i as (select DISTINCT id, created_at::date as date, sender, source_chain, destination_chain, a.symbol, b.symbol as price_symbol, token_name, amt_column, case when amt_column >= 1e6 then 0 else amt_column end as amts, avg_price*amts as amt_usd, avg_price, from final a left outer join price b on created_at::date = b.day and a.token_name = b.symbol ) , Final_II as ( select *, case when token_name in ('USDC','') then amts else amt_usd end as volume, case when token_name is null and amts < 100000 then amts else amt_usd end as volume_ii, case when volume is null then volume_ii else volume end as volume_iii from final_i) select trunc(date,'week') as weekly, case when date >= '2022-01-01' and date < '2022-04-01' then 'Q1-2022' when date >= '2022-04-01' and date < '2022-07-01' then 'Q2-2022' when date >= '2022-07-01' and date < '2022-10-01' then 'Q3-2022' when date >= '2022-10-01' and date < '2023-01-01' then 'Q4-2022' when date >= '2023-01-01' and date < '2023-04-01' then 'Q1-2023' when date >= '2023-04-01' and date < '2023-07-01' then 'Q2-2023' when date >= '2023-07-01' and date < '2023-10-01' then 'Q3-2023' when date >= '2023-10-01' and date < '2024-01-01' then 'Q4-2023' when date >= '2024-01-01' and date < '2024-04-01' then 'Q1-2024' when date >= '2024-04-01' and date < '2024-07-01' then 'Q2-2024' when date >= '2024-07-01' and date < '2024-10-01' then 'Q3-2024' when date >= '2024-10-01' and date < '2025-01-01' then 'Q4-2024' end as type, count(DISTINCT id) as total_tx, count(DISTINCT sender) as users, count(DISTINCT token_name) as tokens, count(DISTINCT source_chain) as sources, count(DISTINCT destination_chain) as destinations, sum(volume_iii) as usd_volume, sum(usd_volume) over (partition by type order by weekly asc) as "Cumulative", usd_volume/users as "Avg Volume Per User" from final_ii where date >= '2023-01-01' group by 1,2

Axelar GMP!