with token_info as ( select token_address, symbol, name, decimals from aptos.core.dim_tokens ) ,prices as ( select hour as p_date, symbol, decimals, token_address, price as avg_price from aptos.price.ez_hourly_token_prices -- GROUP by 1, 2, 3, 4 ) , swaps as ( SELECT BLOCK_TIMESTAMP, TX_HASH, EVENT_DATA, EVENT_TYPE, sender as swapper, CASE WHEN event_data:x_in :: int = 0 THEN TRIM(SPLIT_PART(SPLIT(event_type, ',')[1], ' ',2) :: string, '>') WHEN event_data:x_in :: int != 0 THEN TRIM(SPLIT_PART(SPLIT(event_type, ',')[0], '<', 2) :: string, ' ') END AS token_in, CASE WHEN event_data:y_in :: int = 0 THEN TRIM(SPLIT_PART(SPLIT(event_type, ',')[1], ' ',2) :: string, '>') WHEN event_data:y_in :: int != 0 THEN TRIM(SPLIT_PART(SPLIT(event_type, ',')[0], '<', 2) :: string, ' ') END AS token_out, CASE WHEN event_data : x_in :: int = 0 THEN event_data : y_in :: int ELSE event_data : x_in :: int END AS amount_in_raw, CASE WHEN event_data : y_out :: int = 0 THEN event_data : x_out :: int ELSE event_data : y_out :: int END AS amount_out_raw FROM aptos.core.fact_events a join aptos.core.fact_transactions b using(tx_hash, block_timestamp) WHERE event_address = '0x190d44266241744264b964a37b8f09863167a12d3e70cda39376cfb4e3561e12' AND EVENT_RESOURCE ilike 'Swap%' -- AND EVENT_RESOURCE ilike 'SwapEvent%' -- AND EVENT_MODULE ilike 'liquidity_pool' AND SUCCESS = TRUE -- and tx_hash = '0xd70b1e9a9f0bb92f5ac766d7533171fd19eb69725ad148a52ad7fc737dfe869d' -- and tx_hash = '0xb4ea266da10753a1e6ad07433a9ea4e2ed08e928978c73db3c6fc449b0529231' ) , swaps_main as ( select s.block_timestamp, s.tx_hash, s.token_in, s.swapper, t1.symbol as symbol_in, s.token_out , t2.symbol as symbol_out, amount_in_raw, amount_in_raw / pow(10, t1.decimals) as amount_in, amount_out_raw, amount_out_raw / pow(10,t2.decimals) as amount_out from swaps s left join token_info t1 on s.token_in = t1.token_address left join token_info t2 on s.token_out = t2.token_address ) , final as ( select block_timestamp, tx_hash, swapper, token_in, symbol_in, token_out, symbol_out, -- amount_in_raw, -- amount_in, (amount_in * p1.avg_price) as amount_in_usd, -- amount_out_raw, -- amount_out, (amount_out * p2.avg_price) as amount_out_usd from swaps_main sm left join prices p1 ON sm.symbol_in = p1.symbol and date_trunc('hour', sm.block_timestamp) = p1.p_date left join prices p2 ON sm.symbol_out = p2.symbol and date_trunc('hour', sm.block_timestamp) = p2.p_date GROUP by all ) , userTb as ( SELECT swapper, count(DISTINCT tx_hash) as n_swaps, sum(case when amount_in_usd is not null then amount_in_usd else amount_out_usd end) as volume_usd FROM final WHERE block_timestamp::date >= '2024-01-01' AND block_timestamp::date < current_date() GROUP BY 1 ORDER by 3 DESC ) SELECT CASE when n_swaps <= 10 then 'a. < 10' when n_swaps <= 50 then 'b. 10 ~ 50' when n_swaps <= 100 then 'c. 50 ~ 100' when n_swaps <= 500 then 'd. 100 ~ 500' when n_swaps <= 1000 then 'e. 500 ~ 1000' when n_swaps > 1000 then 'f. >= 1000' end as txs_breakdown, CASE when volume_usd <= 100 then 'a. < $100' when volume_usd <= 1000 then 'b. 100 ~ $1k' when volume_usd <= 10000 then 'c. 1 ~ $10k' when volume_usd <= 50000 then 'd. 10 ~ $50k' when volume_usd <= 100000 then 'e. 50 ~ $100k' when volume_usd <= 500000 then 'f. 100k ~ $0.5m' when volume_usd <= 1000000 then 'g. 0.5 ~ $1m' when volume_usd > 1000000 then 'h. >= $1m' end as distribution, count(DISTINCT swapper) as n_users FROM userTb GROUP by 1,2 ORDER by 2,1

In-Depth First AMM on the Aptos Overview