shadilhourly price tmp
Updated 2022-05-18
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
›
⌄
-- SELECT *
-- from algorand.prices_swap
-- where date(block_hour) = CURRENT_DATE - 1
-- and asset_id = 0
-- with assets as (
-- select swap_to_asset_id as asset_id, count(DISTINCT swapper) as swappers_count, COUNT(DISTINCT tx_group_id) as swaps_count
-- from algorand.swaps
-- where date(block_timestamp) >= '2022-02-01'
-- and swap_from_amount > 0
-- -- and (swap_from_asset_id = 0 or swap_to_asset_id = 0)
-- group by swap_to_asset_id
-- order by swappers_count DESC
-- limit 10
-- ),
with assets as (
SELECT asset_id, asset_name, sum(volume_usd_in_hour) as vol_usd, avg(volatility_measure) as avg_volatility, sum(swaps_in_hour) as vol_swaps
from algorand.prices_swap
where date(block_hour) >= CURRENT_DATE - 30
-- and asset_id != 287867876 -- Opulous
GROUP by asset_id, asset_name HAVING vol_usd > 0
order by vol_usd DESC
limit 10
),
changes as (
SELECT
-- extract('hour', block_hour) as date,
block_hour,
price_usd as asset_price,
asset_name,
lag(asset_price) ignore nulls over(partition by asset_name ORDER BY block_hour ASC) as asset_price_prev,
((asset_price - asset_price_prev)/asset_price)*100 as percent_asset_price
-- ((max(price_usd)-min(price_usd))/min(price_usd))*100 as daily_percent_range_luna,
-- STDDEV(price_usd) as asset_price_stddev
FROM algorand.prices_swap
-- where date(block_hour) >= CURRENT_DATE - 200
Run a query to Download Data