Nexus (PSI ) daily price

nitsNexus (PSI ) daily price

Updated 2022-03-16

-- SELECT

-- date_trunc('day', block_timestamp) as date,

-- event_attributes:"1_contract_address" as token, -- remove, added to verify

-- address_name as token_name, -- can remove, added to verify

-- 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

-- avg(event_attributes:"offer_amount"/pow(10,6)) as usd_offered_avg, -- usd_offered

-- sum(event_attributes:"return_amount"/pow(10,8)) as alt_bought, -- IMPORTANT ORION DECIMAL PLACE IS 8 NOT 6

-- usd_offered/alt_bought as avg_buy_price

-- FROM terra.msg_events

-- LEFT JOIN terra.labels ON address = event_attributes:"1_contract_address" -- you can remove this if not needed

-- WHERE event_type = 'from_contract'

-- AND event_attributes:"0_contract_address" IN ('terra1mxyp5z27xxgmv70xpqjk7jvfq54as9dfzug74m',

-- 'terra1z6tp0ruxvynsx5r9mmcc2wcezz9ey9pmrw5r8g',

-- 'terra1s5wkurdh4sw47lgnk5em4h69v5vh9dncmkhyrg', 'terra1php5m8a6qd68z02t3zpw4jv2pj4vgw4wz0t8mz'

-- ) -- these are Orion LP contract

-- AND event_attributes:"offer_asset" = 'uusd'

-- AND tx_status = 'SUCCEEDED'

-- GROUP BY 1,2,3

SELECT date(block_timestamp) as day, avg(msg_value:execute_msg:swap:belief_price) as avg_price

from terra.msgs

where msg_value:contract = 'terra163pkeeuwxzr0yhndf8xd2jprm9hrtk59xf7nqf' and contains(msg_value, 'swap')

-- where tx_id ilike 'CB051B707347DBA46834C97AC41F283309DF902D090107E934CFCF22E2714824'

GROUP by 1

order by 1 desc

limit 60

Run a query to Download Data

flipside