maybeyonasbLuna-daily_swap_dev
Updated 2021-10-18
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 swap_event as(
select
block_timestamp,tx_id,
event_attributes:"to"::string as user, --"terra1jse8nzxu5uf9tq5m4rw9v0hhqcfutahay6zj74"
-- address_name as pool_name,
event_attributes:"from"::string as pool, --"terra1jxazgm67et0ce260kvrpfv50acuushpjsz2y0p",
event_attributes:"offer_asset"::string as token_in, --"uluna",
event_attributes:"offer_amount" as amount_in, --:3000000,
event_attributes:"ask_asset"::string as token_out, --"terra1kc87mu460fwkqte29rquh4hc20m54fxwtsx7gp",
event_attributes:"return_amount" as amount_out, --:3052869,
event_attributes:"commission_amount" as swap_fee, -- :9186,
event_attributes:"spread_amount" as spread, --:6,
event_attributes:"tax_amount" as tax --:0,
from terra.msg_events
-- join pools on event_attributes:"from" = address or address = event_attributes:"1_contract_address" or address = event_attributes:"0_contract_address"
where tx_status = 'SUCCEEDED' and event_type = 'from_contract'
and event_attributes:"ask_asset" is not null
and event_attributes:"offer_asset" is not null
-- and block_timestamp >= getdate() - interval '5 days'
and pool = 'terra1jxazgm67et0ce260kvrpfv50acuushpjsz2y0p'
),
latest_swap as(
select
block_timestamp,
amount_in/amount_out as price
from swap_event
where
token_in = 'uluna'
)
select
date_trunc('HOUR',block_timestamp) as date,
min(price) as min_price,
max(price) as max_price,
avg(price) as avg_price
Run a query to Download Data