select

fe.block_timestamp,

fe.tx_hash as tx_id,

1 as action_count,

LOWER(fe.event_data:user::string) as address,

1 as quest_step,

'APT' as currency,

fe.event_data:collateral_delta::float / pow(10, 6) as usd_amount,

usd_amount / ap.price as token_amount,

case

when usd_amount >= 75 then TRUE

else FALSE

end as valid,

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

-- we're pulling so many extra dates in:

ROW_NUMBER() OVER (

PARTITION BY address

ORDER BY

ap.hour DESC

) AS prn

from

aptos.core.fact_events fe

LEFT JOIN aptos.core.fact_transactions tx on fe.tx_hash = tx.tx_hash -- get token price

LEFT JOIN (

SELECT

DISTINCT hour,

price

FROM

aptos.price.ez_prices_hourly

WHERE

lower(token_address) = lower('0x1::aptos_coin::aptoscoin')

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

where

-- fe.tx_hash = '0xc840aeff536b960f327d2bdc699b2e347bb485be79c43055c860cfb569a4b472'

-- and