binhachon77. [Hard] Apollo Inflow: LP deposit into Apollo pool
    Updated 2021-12-06
    WITH LP_DEPOSIT AS (SELECT DATE_TRUNC('Day', BLOCK_TIMESTAMP) AS BLOCKTIME, EVENT_ATTRIBUTES:"0_contract_address"::string as LP_ADDRESS, SUM(EVENT_ATTRIBUTES:"0_amount"::float/1e6) as LP_AMOUNT FROM terra.msg_events
    WHERE EVENT_TYPE = 'from_contract'
    AND EVENT_ATTRIBUTES:"1_action" = 'deposit_to_strategy'
    AND EVENT_ATTRIBUTES: "1_contract_address" = 'terra1g7jjjkt5uvkjeyhp8ecdz4e4hvtn83sud3tmh2'

    GROUP BY BLOCKTIME, LP_ADDRESS
    ORDER BY LP_AMOUNT DESC), -- Deposit LP

    LP_WITHDRAW AS (SELECT DATE_TRUNC('Day', BLOCK_TIMESTAMP) AS BLOCKTIME, EVENT_ATTRIBUTES:"lp_token"::string as LP_ADDRESS, SUM(EVENT_ATTRIBUTES:"withdraw_lp_amount"::float/1e6) as LP_AMOUNT FROM terra.msg_events
    WHERE EVENT_TYPE = 'from_contract'
    AND EVENT_ATTRIBUTES:"0_action" = 'withdraw_from_strategy'
    AND EVENT_ATTRIBUTES: "0_contract_address" = 'terra1g7jjjkt5uvkjeyhp8ecdz4e4hvtn83sud3tmh2'

    GROUP BY BLOCKTIME, LP_ADDRESS
    ORDER BY LP_AMOUNT DESC
    ), -- Withdraw LP
    UST_ZAP_OUT AS (SELECT DATE_TRUNC('Day', BLOCK_TIMESTAMP) AS BLOCKTIME, EVENT_ATTRIBUTES:"3_contract_address"::string as LP_ADDRESS,
    SUM((EVENT_ATTRIBUTES:"return_amount"::float + EVENT_ATTRIBUTES:"ust returned")/1e6) as USD_AMOUNT FROM terra.msg_events
    WHERE EVENT_TYPE = 'from_contract'
    AND EVENT_ATTRIBUTES:"0_action" = 'zap_out_of_strategy'
    AND EVENT_ATTRIBUTES: "0_contract_address" = 'terra1g7jjjkt5uvkjeyhp8ecdz4e4hvtn83sud3tmh2'
    GROUP BY BLOCKTIME, LP_ADDRESS),
    UST_ZAP AS
    (SELECT DATE_TRUNC('Day', BLOCK_TIMESTAMP) AS BLOCKTIME, EVENT_ATTRIBUTES:"lp_token"::string as LP_ADDRESS,
    SUM(EVENT_ATTRIBUTES:"4_amount"::float/1e6) as LP_AMOUNT, SUM(EVENT_ATTRIBUTES:"0_amount"::float/1e6) as USD_AMOUNT,
    SUM(EVENT_ATTRIBUTES:"0_amount"::float/1e6)/SUM(EVENT_ATTRIBUTES:"4_amount"::float/1e6) as LP_PRICE FROM terra.msg_events
    WHERE EVENT_TYPE = 'from_contract'
    AND EVENT_ATTRIBUTES:"0_action" = 'zap_into_strategy'
    AND EVENT_ATTRIBUTES: "0_contract_address" = 'terra1g7jjjkt5uvkjeyhp8ecdz4e4hvtn83sud3tmh2'
    GROUP BY BLOCKTIME, LP_ADDRESS), -- Zap into LP

    LP_DEPOSIT_WITH_PRICE AS (SELECT LP_DEPOSIT.BLOCKTIME AS BLOCKTIME, LP_DEPOSIT.LP_ADDRESS AS LP_ADDRESS, LP_DEPOSIT.LP_AMOUNT AS LP_AMOUNT, UST_ZAP.LP_PRICE AS LP_PRICE,
    LP_DEPOSIT.LP_AMOUNT*UST_ZAP.LP_PRICE AS USD_AMOUNT FROM LP_DEPOSIT LEFT JOIN UST_ZAP
    ON LP_DEPOSIT.BLOCKTIME = UST_ZAP.BLOCKTIME AND LP_DEPOSIT.LP_ADDRESS = UST_ZAP.LP_ADDRESS), -- Find the average LP price
    Run a query to Download Data