ea1371Cumulative number of pool input transactions
    Updated 2022-12-07
    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