dan-smith2023-02-02 01:47 PM
    Updated 2023-02-02
    WITH
    addliquidity AS (
    SELECT
    DECODED_LOG:provider AS provider
    ,SUM(DECODED_LOG:token_amounts[0] / 1e18) AS eth_add
    ,SUM(DECODED_LOG:token_amounts[1] / 1e18) AS frxETH_add
    FROM ethereum.core.fact_decoded_event_logs
    WHERE
    (EVENT_NAME = 'AddLiquidity')
    AND CONTRACT_ADDRESS = '0xa1f8a6807c402e4a15ef4eba36528a3fed24e577'
    GROUP BY 1
    ),

    removeliquidity AS (
    SELECT
    DECODED_LOG:provider AS provider
    ,-SUM(DECODED_LOG:token_amounts[0] / 1e18) AS eth_rem
    ,-SUM(DECODED_LOG:token_amounts[1] / 1e18) AS frxETH_rem
    FROM ethereum.core.fact_decoded_event_logs
    WHERE
    (EVENT_NAME = 'RemoveLiquidity')
    AND CONTRACT_ADDRESS = '0xa1f8a6807c402e4a15ef4eba36528a3fed24e577'
    GROUP BY 1
    ),

    currentliquidity AS (
    SELECT
    a.provider
    ,eth_add + COALESCE(eth_rem,0) AS eth
    ,frxETH_add + COALESCE(frxETH_rem,0) AS frxETH
    FROM addliquidity a
    LEFT JOIN removeliquidity r ON a.provider = r.provider
    )

    SELECT *
    FROM currentliquidity
    Run a query to Download Data