select

fe.block_timestamp,

fe.tx_hash as tx_id,

1 as action_count,

LOWER(tx.sender) as address,

1 as quest_step,

'APT' as currency,

fe.event_data:amount_in::number / pow(10, 8) * p.price as usd_amount,

tx.payload:type_arguments[0]::string AS asset,

case when lower(tx.payload:type_arguments[0]::string) = lower('0x1::aptos_coin::AptosCoin') then fe.event_data:amount_in::number / pow(10, 8)

when lower(tx.payload:type_arguments[0]::string) = lower('0xfaf4e633ae9eb31366c9ca24214231760926576c7b625313b3688b5e900731f6::staking::StakedThalaAPT') then fe.event_data:amount_in::number / pow(10, 8)

when lower(tx.payload:type_arguments[0]::string) = lower('0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::USDC') then fe.event_data:amount_in::number / pow(10, 8) / ap.price

when lower(tx.payload:type_arguments[0]::string) = lower('0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::USDT') then fe.event_data:amount_in::number / pow(10, 8) / ap.price

else usd_amount / ap.price

end as token_amount,

TRUE as valid,

coalesce(tx.gas_used * tx.gas_unit_price / pow(10, 8),0) as fee_amount

from aptos.core.fact_events fe

JOIN aptos.core.fact_transactions tx

on fe.tx_hash = tx.tx_hash

LEFT JOIN APTOS.PRICE.EZ_PRICES_HOURLY p

ON p.hour = date_trunc('hour', fe.block_timestamp)

and lower(p.token_address) = lower(tx.payload:type_arguments[0]::string) -- get the aptos token price

LEFT JOIN APTOS.PRICE.EZ_PRICES_HOURLY ap

ON date_trunc('hour', ap.hour) = date_trunc('hour', fe.block_timestamp)

and lower(ap.token_address) = lower('0x1::aptos_coin::aptoscoin')

where fe.event_address ='0x48271d39d0b05bd6efca2278f22277d6fcc375504f9839fd73f74ace240861af'

and fe.event_resource like 'SwapEvent%'

and LOWER(tx.sender) in (:userAddresses)

and fe.block_timestamp :: date >= :startsAt

and tx.block_timestamp :: date >= :startsAt