KaskoazulAsset price with swaps
Updated 2022-04-05
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
›
⌄
SELECT
date_trunc('day', block_timestamp::date as fecha, -- truncate date to days
event_attributes:"ask_asset" as token, -- token contract address
address_name as token_name, -- token contract name
count(distinct tx_id) as buy_counts,
count(distinct event_attributes:sender) as buyer_counts,
sum(event_attributes:"offer_amount"/pow(10,6)) as usd_offered, -- usd_offered
sum(event_attributes:"return_amount"/pow(10,6)) as alt_bought, -- NEED TO CHANGE DECIMAL POINT IF IT IS ORION
usd_offered/alt_bought as avg_buy_price -- average daily price of token
FROM terra.msg_events
LEFT JOIN terra.labels ON address = event_attributes:"ask_asset" -- LEFT JOIN to attach address name to tokens
WHERE event_type = 'from_contract'
AND event_attributes:"offer_asset" = 'uusd' -- UST offered / Q:why do you need this? A:otherwise you cannot calculate price accurately
AND tx_status = 'SUCCEEDED' -- need to filter by successful transactions
AND CURRENT_DATE - date(block_timestamp) < 7 -- filtering out observation in last 7 days / change according to your analyses
---if you are finding the price of 1 token only
AND event_attributes:"ask_asset" = 'terra13zx49nk8wjavedjzu8xkk95r3t0ta43c9ptul7' -- Change by token contract address
--- if you are finding the price of > 1 token
-- AND event_attributes:"ask_asset" IN ('terra13zx49nk8wjavedjzu8xkk95r3t0ta43c9ptul7', 'terra12897djskt9rge8dtmm86w654g7kzckkd698608', 'terra1dh9478k2qvqhqeajhn75a2a7dsnf74y5ukregw')
GROUP BY 1,2,3
order by 1 ASC
Run a query to Download Data