shaunoffLockdrop Total per Hour copy
Updated 2023-03-10
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
›
⌄
-- forked from f9c9b110-85b8-46ab-adb6-67bec5c620bd
with table1 as (select
date_trunc('hour', block_timestamp) as day_hour,
sum(attributes:wasm:"amount") / pow(10, 6) as luna
from terra.core.ez_messages
where date(block_timestamp) > '2023-03-01'
and attributes:wasm:"_contract_address" = 'terra155pkerrnlufru6h9nt5pxlq6fl8jqvgwwnds8wxjr6danfphulrs9e82gq'
and attributes:wasm:"action" = 'deposit'
group by day_hour
),
table2 as (select
day_hour,
sum(LUNA) over (order by day_hour) as cum_luna,
cum_LUNA / 80000000 as capa_luna_price
from table1
),
table3 as (select
date_trunc('hour', block_timestamp) as day_hour,
avg(message_value:msg:swap:"belief_price") as luna_price
from terra.core.ez_messages
where date(block_timestamp) > '2023-03-01'
and message_value:"contract" = 'terra1fd68ah02gr2y8ze7tm9te7m70zlmc7vjyyhs6xlhsdmqqcjud4dql4wpxr'
and message_value:msg:swap:offer_asset:info:native_token:denom = 'ibc/B3504E092456BA618CC28AC671A71FB08C6CA0FD0BE7C8A5B5A3E2DD933CC9E4'
group by day_hour
order by day_hour
),
final as (select
table2.day_hour, cum_luna,
luna_price * capa_luna_price as capa_price
from table2
left outer join table3 on table2.day_hour = table3.day_hour
)