prices as (

select hour,price from aptos.price.ez_prices_hourly where symbol='APT'

),

info as (

select

trunc(block_timestamp,'hour') as date,

case when payload_function ilike '%0x5bbf0aa7a758ae8d71e9c2dfddf36ea56af76473b8dc02cff45287b736dd4e13%' then 'USDt'

when payload_function ilike '%0xd095c52a639b34eb1bcf5043eb2083851ee7c325d645202fc34351dd591e4a0b%' then 'APT'

else 'USDt' end as asset,

count(distinct tx_hash) as action,

count(distinct sender) as users,

sum(case when payload_function not ilike '%0xd095c52a639b34eb1bcf5043eb2083851ee7c325d645202fc34351dd591e4a0b%' then REGEXP_SUBSTR(payload:arguments, '"([0-9]+)"', 1, 1, 'e', 1)/pow(10,6) else 0 end) as usdt_investment,

sum(case when payload_function ilike '%0xd095c52a639b34eb1bcf5043eb2083851ee7c325d645202fc34351dd591e4a0b%' then REGEXP_SUBSTR(payload:arguments, '"([0-9]+)"', 1, 1, 'e', 1)/pow(10,8) else 0 end) as apt_investment

from aptos.core.fact_transactions x

where block_timestamp>=trunc(current_date,'day') --and event_module in ('ido','vesting','buy')

and (payload_function ilike '%0x5bbf0aa7a758ae8d71e9c2dfddf36ea56af76473b8dc02cff45287b736dd4e13%'

or payload_function ilike '%0xd095c52a639b34eb1bcf5043eb2083851ee7c325d645202fc34351dd591e4a0b%'

or payload_function ilike '%0x8bd3a8b20df6acdf55e19b4a84033437ccea63a3857f133e70579b90dc9295f2%')

group by 1,2

),

final as (

select date, asset, action, sum(action) over (partition by asset order by date) as total_actions,

users, sum(users) over (partition by asset order by date) as total_users,

usdt_investment,

apt_investment,

usdt_investment+(apt_investment*price) as invested_volume_usd,

sum(invested_volume_usd) over (partition by asset order by date) as total_invested_in_usd

from info i join prices p on i.date=p.hour

order by 1 desc,2