SajjadiiiCopy of Add Db potw
Updated 2022-12-08Copy 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
›
⌄
-- Join Pool = lp_tokens_minted add both assets
-- Join Swap Extern Amount In = pool_joined -- singleside
-- Exit Pool = lp_tokens_burned \\ pool_exited \\ lp_tokens_burned
WITH stOSMO_db AS(
SELECT block_timestamp::date AS date , 'Double Side' AS LP_sided ,'stOSMO/OSMO' AS pool_name ,
COUNT(DISTINCT tx_id)AS tx_count ,
COUNT(DISTINCT liquidity_provider_address) AS users_count ,
sum(amount/pow(10,DECIMAL)) AS amount_usd,
sum(tx_count) OVER (ORDER BY DATE ASC) AS cumu_tx,
sum(users_count) OVER (ORDER BY DATE ASC) AS cumu_users,
sum(amount_usd) OVER (ORDER BY DATE ASC) AS cumu_volume
FROM osmosis.core.fact_liquidity_provider_actions
WHERE block_timestamp::date BETWEEN '2022-10-01' AND '2022-11-01'
AND currency = 'gamm/pool/833'
AND action = 'lp_tokens_minted' --add both assets
AND pool_id[0] = '833'
AND tx_status = 'SUCCEEDED'
GROUP BY 1 , 2 , 3
) ,
IST_db AS (
SELECT block_timestamp::date AS date , 'Double Side' AS LP_sided ,'IST/OSMO' AS pool_name ,
COUNT(DISTINCT tx_id)AS tx_count ,
COUNT(DISTINCT liquidity_provider_address) AS users_count ,
sum(amount/pow(10,DECIMAL)) AS amount_usd,
sum(tx_count) OVER (ORDER BY DATE ASC) AS cumu_tx,
sum(users_count) OVER (ORDER BY DATE ASC) AS cumu_users,
sum(amount_usd) OVER (ORDER BY DATE ASC) AS cumu_volume
FROM osmosis.core.fact_liquidity_provider_actions
WHERE block_timestamp::date BETWEEN '2022-10-01' AND '2022-11-01'
AND currency = 'gamm/pool/837'
AND action = 'lp_tokens_minted' --add both assets
AND pool_id[0] = '837'
AND tx_status = 'SUCCEEDED'
GROUP BY 1 , 2 , 3
Run a query to Download Data