select

tr.block_timestamp,

tr.tx_hash as tx_id,

1 as action_count,

LOWER(tr.account_address) as address,

1 as quest_step,

'APT' as currency,

tr.amount / pow(10, 8) 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_transfers tr

join aptos.core.fact_events fe on tr.tx_hash = fe.tx_hash

and tr.amount = fe.event_data:amount_in :: float

LEFT JOIN aptos.core.fact_transactions tx on tr.tx_hash = tx.tx_hash

where

LOWER(tr.account_address) in (:userAddresses)

and tr.block_timestamp :: date >= :startsAt

and fe.block_timestamp :: date >= :startsAt

and tx.block_timestamp :: date >= :startsAt

-- tr.block_timestamp :: date >= current_Date - 10

-- and fe.block_timestamp :: date >= current_Date - 10

-- and tx.block_timestamp :: date >= current_Date - 10

and fe.event_module = 'controller'

and fe.event_resource ilike 'WithdrawEvent%'

and tr.token_address ilike '0x1::aptos_coin::AptosCoin'

and tr.tx_hash not in (select distinct tx_id from HEVO.BRONZE_EARN_QUESTS_TREASURY_2024.QUEST_OUTPUTS)

':questId' as quest_id,

block_timestamp,

tx_id,

action_count,

address,

valid,