adriaparcerisasOsmosis: In The Stars 3
Updated 2022-05-31
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
›
⌄
--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,
convert_timezone('America/Los_Angeles', 'America/New_York', block_timestamp) as est_date,
--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
hour(est_date) as "HOUR",
sum(amount) as hourly_volume,
avg(hourly_volume) over (order by "HOUR") as "Average volume"
from info
where dayname(block_timestamp) = 'Fri'
--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