ea1371Cumulative number of pool input transactions
Updated 2022-12-07
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
›
⌄
with Pool_entrance_table as(
WITH PRICE_TABLE AS (
SELECT
DATE(RECORDED_AT) AS DATE,
SYMBOL,
AVG(PRICE) AS DAILY_PRICE
FROM osmosis.core.dim_prices
GROUP BY 1,2
ORDER BY 1)
SELECT
BLOCK_TIMESTAMP,
TX_ID,
LIQUIDITY_PROVIDER_ADDRESS,
ACTION,
POOL_ID,
CASE WHEN PROJECT_NAME = 'INJ' THEN AMOUNT/POW(10,18) ELSE AMOUNT/POW(10,DECIMAL) END AS JUSTIFIED_AMOUNT,
CASE WHEN PROJECT_NAME = 'INJ' THEN AMOUNT*DAILY_PRICE/POW(10,18) ELSE AMOUNT*DAILY_PRICE/POW(10,DECIMAL) END AS USD_AMOUNT,
CURRENCY,
PROJECT_NAME
FROM osmosis.core.fact_liquidity_provider_actions
FULL JOIN osmosis.core.dim_labels ON CURRENCY = ADDRESS
FULL JOIN PRICE_TABLE ON SYMBOL = PROJECT_NAME AND DATE = BLOCK_TIMESTAMP::DATE
WHERE TX_STATUS = 'SUCCEEDED')
select date(block_timestamp)as"date",CURRENCY,COUNT(DISTINCT TX_ID) as "Number of transactions",sum(JUSTIFIED_AMOUNT)as "sum amount",
sum("Number of transactions")over(partition by CURRENCY ORDER by "date") as "cumulative number of transactions",
sum("sum amount")over(partition by CURRENCY ORDER by "date") as "cumulative sum amount",
count (DISTINCT LIQUIDITY_PROVIDER_ADDRESS) as "Number of users",sum(USD_AMOUNT)as "Sum of the amount"
from Pool_entrance_table
where ACTION='pool_joined' and POOL_ID=' 634' and BLOCK_TIMESTAMP>='2022-11-29'
GROUP by 1,2
Run a query to Download Data