keshanBurn the House Down - tokens
Updated 2022-06-22
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
›
⌄
with hourly_price as (select date_trunc('hour', block_timestamp) as hour,
(case when to_currency='Osmosis' then from_currency when from_currency='Osmosis' then to_currency else null end) as token,
avg(price) as price
from (select block_timestamp,
(case when from_currency='uosmo' then (from_amount/pow(10, from_decimal)) / (to_amount/pow(10, to_decimal)) when to_currency='uosmo' then (to_amount/pow(10, to_decimal)) / (from_amount/pow(10, from_decimal)) else null end) as price,
(case when t.label is null then to_currency else t.label end) as to_currency,
(case when f.label is null then from_currency else f.label end) as from_currency
from osmosis.core.fact_swaps
left join osmosis.core.dim_labels t on t.address=to_currency
left join osmosis.core.dim_labels f on f.address=from_currency)
where price is not null
group by hour, token)
select week, label, sum(txs) as txs, sum(amount_osmo) as amount_osmo, sum(amount) as amount from (select date_trunc('week', block_timestamp) as week,
count(tx_id) as txs,
sum(case when raw_metadata[0]:exponent is null
then regexp_substr(attribute_value, '^[0-9]+')/pow(10, (case when startswith(regexp_replace(attribute_value, regexp_substr(attribute_value, '^[0-9]+')), 'gamm') then 18 else 6 end)) --1e18 ??? 6E4850CD91A26D0AC59E0D32B887A2BA737FA4568724E34EB89F84F8F817AD08 but 7CC98BE02C5A711C28FA1405ECE6E68B1D1495DF70A835D45BDBCB8C78E90733 1e6
else regexp_substr(attribute_value, '^[0-9]+')/pow(10, raw_metadata[1]:exponent) end) as amount,
sum(case when raw_metadata[0]:exponent is null
then regexp_substr(attribute_value, '^[0-9]+')/pow(10, (case when startswith(regexp_replace(attribute_value, regexp_substr(attribute_value, '^[0-9]+')), 'gamm') then 18 else 6 end)) --1e18 ??? 6E4850CD91A26D0AC59E0D32B887A2BA737FA4568724E34EB89F84F8F817AD08 but 7CC98BE02C5A711C28FA1405ECE6E68B1D1495DF70A835D45BDBCB8C78E90733 1e6
else regexp_substr(attribute_value, '^[0-9]+')/pow(10, raw_metadata[1]:exponent) end * (case when label='Cerberus' and hour <= '2022-03-19 04:00:00.000' then 0.00005 else price end)) as amount_osmo, -- Cerberus had unbelivebly high price at the beginning hours may be due to less liquidity on DEX
label
from osmosis.core.fact_msg_attributes
left join osmosis.core.dim_labels on split(attribute_value, regexp_substr(attribute_value, '^[0-9]+'))[1] = address
left join hourly_price on hour=date_trunc('hour', block_timestamp) and label=token
where msg_type='burn' and attribute_key='amount'
group by week, label, attribute_value)
where label is not null
group by week, label