adriaparcerisasOsmosis: In The Stars
Updated 2022-05-30
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
›
⌄
--Stargaze Zone is an IBC NFT launchpad and marketplace, and new launchpad projects drop on mint every Friday at 4:00 PM EST.
--Does the volume of swaps involving Stars change around this time?
--Analyze the volume of swaps involving STARS from March 20th until present.
--Hint: Use the msg_attributes table and select transactions that have an appropriate msg_type for swaps. The token address for STARS and other tokens can be found by joining to the labels table.
WITH
info as (
select
tx_id,
block_timestamp,
--attribute_key,
RIGHT(attribute_value, LENGTH(attribute_value) - LENGTH(SPLIT_PART(TRIM(REGEXP_REPLACE(attribute_value, '[^[:digit:]]', ' ')), ' ', 0))) AS currency,
SPLIT_PART(TRIM(REGEXP_REPLACE(
attribute_value,
'[^[:digit:]]',
' ')), ' ', 0)/pow(10,6) AS amount
from osmosis.core.fact_msg_attributes
where
RIGHT(attribute_value, LENGTH(attribute_value) - LENGTH(SPLIT_PART(TRIM(REGEXP_REPLACE(attribute_value, '[^[:digit:]]', ' ')), ' ', 0))) ='ibc/987C17B11ABC2B20019178ACE62929FE9840202CE79498E29FE8E5CB02B7C0A4'
and block_timestamp>='2022-03-20'
and msg_type='token_swapped'
and attribute_key in ('tokens_in','tokens_out')
)
SELECT
trunc(block_timestamp,'day') as date,
--attribute_key,
sum(amount) as daily_volume,
sum(daily_volume) over (order by date) as cum_volume
from info
--join osmosis.core.dim_labels y on x.currency=y.address
--where project_name='STARS'
group by 1--,2
order by 1 asc
Run a query to Download Data