PromotedPawn2. Protocol token price_vAlternativa
Updated 2022-03-16Copy Reference Fork
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
›
⌄
-- Symbol: in ('MINE', 'LOOP', 'HALO', 'SPEC', 'TNS', 'PSI')
-- Pylon (MINE): terra1kcthelkax4j9x8d3ny6sdag0qmxxynl3qtcrpy
-- Loop (Loop): terra1nef5jf6c7js9x6gkntlehgywvjlpytm7pcgkn4
-- Angel protocol (HALO): terra1w8kvd6cqpsthupsk4l0clwnmek4l3zr7c84kwq
-- Spectrum (SPEC): terra1s5eczhe0h0jutf46re52x5z4r03c8hupacxmdr
-- TNS (TNS): terra1g77epr507pp572hnhaprjuz9fp6yn3e4mgyxar
-- Nexus (PSI): terra12897djskt9rge8dtmm86w654g7kzckkd698608
--select distinct(event_type) from terra.msg_events --limit 10
SELECT
date_trunc('day', block_timestamp) as date, -- 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" IN ('terra1kcthelkax4j9x8d3ny6sdag0qmxxynl3qtcrpy', 'terra1nef5jf6c7js9x6gkntlehgywvjlpytm7pcgkn4', 'terra1w8kvd6cqpsthupsk4l0clwnmek4l3zr7c84kwq', 'terra1s5eczhe0h0jutf46re52x5z4r03c8hupacxmdr', 'terra1g77epr507pp572hnhaprjuz9fp6yn3e4mgyxar', 'terra12897djskt9rge8dtmm86w654g7kzckkd698608')
--- 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