with main AS (select

block_timestamp,

TX_HASH,

version,

case

when t1.EVENT_DATA:is_sell = false then 'buy'

else 'sell'

end AS Action,

t1.EVENT_DATA:swapper AS swapper,

case when t1.EVENT_DATA:is_sell = false then t1.EVENT_DATA:input_amount / 1e8

else t1.EVENT_DATA:quote_volume / 1e8 end AS APT_Size,

Apt_Size*price AS USD_size,

t2.EVENT_DATA:market_metadata:market_address AS market_address,

t1.EVENT_DATA:market_id AS market_id,

t2.event_data:instantaneous_stats:market_cap / 1e8 AS Mcap,

mcap*price AS Mcap_USD,

t2.event_data:instantaneous_stats:fully_diluted_value / 1e8 AS fdv,

fdv*price AS fdv_USD,

t2.event_data:instantaneous_stats:total_value_locked / 1e8 AS TVL,

TVL*price AS TVL_USD,

aptos.core.fact_events t1

inner join aptos.core.fact_events t2 using(TX_HASH, version)

inner join aptos.price.ez_prices_hourly on hour=date_trunc('hour',t1.block_timestamp) and TOKEN_ADDRESS='0x1::aptos_coin::AptosCoin'

t2.EVENT_RESOURCE = 'State'

and t1.EVENT_RESOURCE = 'Swap'

and t1.event_module = 'emojicoin_dot_fun')

select

sum(APT_Size) AS "Volume (APT)",

sum(USD_size) AS "Volume (USD)",

count(*) AS TX,

count(distinct swapper ) AS Swappers,