SELECT

distinct tx_hash,

block_timestamp,

coalesce(label,'unknown dex') as protocol

FROM aptos.core.fact_events events

left join aptos.core.dim_labels labs

on events.event_address = labs.address

WHERE --event_address = '0x190d44266241744264b964a37b8f09863167a12d3e70cda39376cfb4e3561e12'

event_resource ilike 'Swap%'

and block_timestamp > current_date - {{daysago}}

),

fact_swap as (

SELECT

BLOCK_TIMESTAMP,

tx_hash,

a_in.account_address as address_in,

a_out.account_address as address_out,

a_l_in.NAME as symbol_in,

a_l_out.NAME as symbol_out,

a_l_in.DECIMALS as DECIMALS_in,

a_l_out.DECIMALS as DECIMALS_out,

a_in.AMOUNT/pow(10,a_l_in.DECIMALS) as AMOUNT_in,

a_out.AMOUNT/pow(10,a_l_out.DECIMALS) as AMOUNT_out

FROM aptos.core.fact_transfers a_out

JOIN aptos.core.fact_transfers a_in using(BLOCK_TIMESTAMP, tx_hash)

LEFT JOIN aptos.core.dim_tokens a_l_in on a_in.TOKEN_ADDRESS = a_l_in.TOKEN_ADDRESS

LEFT JOIN aptos.core.dim_tokens a_l_out on a_out.TOKEN_ADDRESS = a_l_out.TOKEN_ADDRESS

WHERE TX_HASH in (SELECT tx_hash FROM swapTxs)

AND a_out.block_timestamp > current_date - {{daysago}}

AND a_in.block_timestamp > current_date - {{daysago}}

AND a_in.TRANSFER_EVENT = 'DepositEvent'

AND a_out.TRANSFER_EVENT = 'WithdrawEvent'

AND a_out.SUCCESS = 'true'

AND (amount_in > 0 and amount_out > 0)

), agg as (