Afonso_Diaz2024-05-02 09:29 PM
Updated 2024-05-02
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
›
⌄
-- Checking token prices
with pricet as (
select
hour::date as date,
token_address,
avg(price) as price_usd
from aptos.price.fact_hourly_token_prices
group by 1, 2
),
--every LiquidSwap has event_resource like SwapEvent and event_module = event_module
--also main LiquidSwap contract is: 0x5a97986a9d031c4567e15b797be516910cfcb4156312482efc6a19c0a30c948
-- to get token_contracts we used a regular expression to find contracts that started with 0x. also to find which contract is inflow contract, we should look at x_in, y_in or x_out, y_out. that can represent which one is the input contract and which one is the output contract. so we used if statement to determine which one we should consider, the same happended to token_out_contract
-- to find amount_in and amount_out, we just need to see which one (x_in, y_in or x_out, y_out) is not equals to 0. that would be our desired value to consider
main as (
select
block_number,
tx_hash,
block_timestamp,
sender,
'0x' || trim(regexp_substr(event_type, '0x([0-9a-zA-Z:_]+)', 1, iff(event_data:x_in::int != 0, 2, 3), 'e', 1)) as token_in_contract,
'0x' || trim(regexp_substr(event_type, '0x([0-9a-zA-Z:_]+)', 1, iff(event_data:x_out::int != 0, 2, 3), 'e', 1)) as token_out_contract,
iff(event_data:x_in::int != 0, event_data:x_in, event_data:y_in)::int as amount_in_unadj,
iff(event_data:x_out::int != 0, event_data:x_out, event_data:y_out)::int as amount_out_unadj
from aptos.core.fact_events a
join aptos.core.fact_transactions b
using (tx_hash)
where account_address = '0x5a97986a9d031c4567e15b797be516910cfcb4156312482efc6a19c0a30c948'
and event_resource like 'SwapEvent%'
and event_module = 'liquidity_pool'
and success = 'TRUE'
and tx_type = 'user_transaction'
),
-- in final part, we can now caclulate price_usd, amount_in and amount_out (adjusted), pair and etc...
QueryRunArchived: QueryRun has been archived