Flipside Data ScienceSwap on Liquidswap
    Updated 2024-05-29
    with t0 as (
    select
    tx.block_timestamp,
    tx.tx_hash as tx_id,
    1 as action_count,
    LOWER(tx.sender) as address,
    1 as quest_step,
    'APT' as currency,
    tx.payload:arguments[0]::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 tx.payload:arguments[0]::number / pow(10, 8)
    when lower(tx.payload:type_arguments[0]::string) = lower('0xfaf4e633ae9eb31366c9ca24214231760926576c7b625313b3688b5e900731f6::staking::StakedThalaAPT') then tx.payload:arguments[0]::number / pow(10, 8)
    when lower(tx.payload:type_arguments[0]::string) = lower('0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::USDC') then tx.payload:arguments[0]::number / pow(10, 8) / ap.price
    when lower(tx.payload:type_arguments[0]::string) = lower('0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::USDT') then tx.payload:arguments[0]::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_transactions tx
    LEFT JOIN APTOS.PRICE.EZ_PRICES_HOURLY p
    ON p.hour = date_trunc('hour', tx.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', tx.block_timestamp)
    and lower(ap.token_address) = lower('0x1::aptos_coin::aptoscoin')


    WHERE
    payload_function= '0x99465840349525b3d648e609eacb056d5b61e3915e8a3972b431afc26e3f05a1::router::swap_coin_for_coin_x1'
    and success = TRUE
    and LOWER(tx.sender) in (:userAddresses)
    and tx.block_timestamp :: date >= :startsAt
    -- and tx.block_timestamp > current_date - 3
    )
    QueryRunArchived: QueryRun has been archived