RayyykPool of The Week 1
Updated 2022-12-07Copy Reference Fork
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
›
⌄
with price as (select recorded_at::date as date,
address,
symbol,
avg(price) as asset_price
from osmosis.core.dim_prices a
join osmosis.core.dim_labels b on a.symbol = b.project_name
where symbol != 'IOV'
group by 1,2,3
union all
select recorded_at::date as date,
'ibc/D176154B0C63D1F9C6DCFB4F70349EBF2E2B5A87A05902F57A6AE92B863E9AEC',
'stOSMO',
avg(price) as asset_price
from osmosis.core.dim_prices a
join osmosis.core.dim_labels b on a.symbol = b.project_name
where symbol = 'OSMO'
group by 1,2,3),
table_1 as (select date_trunc('day', block_Timestamp) as day,
count(distinct(tx_id)) as deposit_tx,
count(distinct(liquidity_provider_address)) as deposit_wallet,
sum(amount * asset_price / pow(10,decimal)) as deposit_volume,
avg(amount * asset_price / pow(10,decimal)) as depopsit_avg_volume
from osmosis.core.fact_liquidity_provider_actions a
join price c on a.currency = c.address and a.block_timestamp::date = c.date
where action = 'pool_joined'
and pool_id = '833'
and tx_status = 'SUCCEEDED'
and block_timestamp >= '2022-11-01'
group by 1),
table_2 as (select date_trunc('day', block_Timestamp) as day,
count(distinct(tx_id)) as withdraw_tx,
count(distinct(liquidity_provider_address)) as withdraw_wallet,
sum(amount * asset_price / pow(10,decimal)) as withdraw_volume
from osmosis.core.fact_liquidity_provider_actions a
Run a query to Download Data