alirspow-01-01
    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/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_Providers
    AS (
    SELECT --DATE_TRUNC('day', block_timestamp) AS DATE
    --action
    COUNT(DISTINCT tx_id) AS "Number of LPs"
    ,COUNT(DISTINCT LIQUIDITY_PROVIDER_ADDRESS) AS "Number of LPers"
    ,sum(amount * "USD Price" / pow(10, DECIMAL)) AS "Total Volume"
    ,"Number of LPs" / "Number of LPers" AS "AVG LP per Wallet"
    FROM osmosis.core.fact_liquidity_provider_actions lp
    INNER JOIN price ON lp.currency = price.address
    AND lp.block_timestamp::DATE = price.DATE
    Run a query to Download Data