alirspow-01-02
Updated 2022-12-08
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 "USD Price"
FROM osmosis.core.dim_prices price
INNER JOIN osmosis.core.dim_labels lab ON price.symbol = lab.project_name
GROUP BY 1
,2
,3
UNION ALL
SELECT recorded_at::DATE AS DATE
,'ibc/D176154B0C63D1F9C6DCFB4F70349EBF2E2B5A87A05902F57A6AE92B863E9AEC' AS "New address"
,'stOSMO' AS "New symbol"
,avg(price) AS "USD Price"
FROM osmosis.core.dim_prices price
INNER JOIN osmosis.core.dim_labels lab ON price.symbol = lab.project_name
WHERE symbol = 'OSMO'
GROUP BY 1
,2
,3
)
,LP_OUT
AS (
SELECT DATE_TRUNC('day', block_timestamp) AS DATE
,case when DATE < '2022-11-11' then 'Before Pool of the Week'
when date >= '2022-11-11' then 'After Pool of the Week'
end as "Time"
,action
,COUNT(DISTINCT tx_id) AS "Number of LPs OUT"
,COUNT(DISTINCT LIQUIDITY_PROVIDER_ADDRESS) AS "Number of LPers OUT"
,sum(amount * "USD Price" / pow(10, DECIMAL)) AS "Total Volume OUT"
--,"Number of LPs" / "Number of LPers" AS "AVG LP per Wallet"
Run a query to Download Data