Flipside Data ScienceSwap on Liquidswap
Updated 2024-05-29
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
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