alirspow-03-02
    Updated 2022-12-08
    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/F4A070A6D78496D53127EA85C094A9EC87DFC1F36071B8CCDDBD020F933D213D' AS "New address"
    ,symbol
    ,avg (price) as USDPrice
    FROM osmosis.core.dim_prices price
    where symbol = 'WBNB'
    --INNER JOIN osmosis.core.dim_labels lab ON price.symbol = lab.project_name
    GROUP BY 1
    ,2
    ,3
    )
    ,LP_OUT
    AS (
    SELECT DATE_TRUNC('day', block_timestamp) AS DATE
    ,case when DATE < '2022-11-22' then 'Before Pool of the Week'
    when date >= '2022-11-22' 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