binhachon93. [Loop] Post-Airdrop Behavior: Group by amount received
    Updated 2021-10-18
    --------------------CALCULATE AIRDROP CLAIM-----------------------------------------------------------------------------------------------------------
    with GENESIS_AIRDROP_CLAIM as (SELECT EVENT_ATTRIBUTES:"to"::string as ADDRESS, SUM(EVENT_ATTRIBUTES:"0_amount"/1e6) AS AMOUNT from terra.msg_events
    WHERE EVENT_ATTRIBUTES:"0_contract_address" = 'terra1atch4d5t25csx7ranccl48udq94k57js6yh0vk'
    AND EVENT_ATTRIBUTES:"0_action" = 'claim'
    GROUP BY ADDRESS),
    --------------------CALCULATE BUY SELL AMOUNT-----------------------------------------------------------------------------------------------------------
    LP_SWAP_TABLE AS(
    SELECT --BLOCK_TIMESTAMP, TX_ID, --SWAP UST FOR OTHER PAIR
    EVENT_ATTRIBUTES:"to"::string AS ADDRESS,
    0 AS SELL_AMOUNT,
    --EVENT_ATTRIBUTES:"offer_amount"::float/1e6 AS UST_AMOUNT,
    EVENT_ATTRIBUTES:"return_amount"::float/1e6 AS BUY_AMOUNT
    FROM terra.msg_events
    WHERE EVENT_TYPE = 'from_contract'
    AND EVENT_ATTRIBUTES:"0_action"::string = 'swap'
    AND EVENT_ATTRIBUTES:"0_contract_address"::string = 'terra106a00unep7pvwvcck4wylt4fffjhgkf9a0u6eu' -- INPUT LP CONTRACT HERE
    UNION ALL
    SELECT --BLOCK_TIMESTAMP, TX_ID, -- SWAP OTHER PAIR TO UST
    EVENT_ATTRIBUTES:"from"::string AS ADDRESS,
    --EVENT_ATTRIBUTES:"return_amount"::float/1e6 AS UST_AMOUNT,
    EVENT_ATTRIBUTES:"offer_amount"::float/1e6 AS SELL_AMOUNT,
    0 AS BUY_AMOUNT
    FROM terra.msg_events
    WHERE EVENT_TYPE = 'from_contract'
    AND EVENT_ATTRIBUTES:"1_action"::string = 'swap'
    AND EVENT_ATTRIBUTES:"1_contract_address"::string = 'terra106a00unep7pvwvcck4wylt4fffjhgkf9a0u6eu' -- INPUT LP CONTRACT HERE
    ),
    BUY_SELL_LOOP AS(
    SELECT ADDRESS, SUM(BUY_AMOUNT) AS BUY_AMOUNT, SUM(SELL_AMOUNT) AS SELL_AMOUNT FROM LP_SWAP_TABLE
    GROUP BY ADDRESS
    ),
    --------------------CALCULATE LP PROVIDER-----------------------------------------------------------------------------------------------------------
    PROVIDE_LIQUIDITY AS(
    SELECT MSG_VALUE:"sender"::string as ADDRESS,
    MSG_VALUE:"execute_msg":"provide_liquidity":"assets"[0]:"amount"::float/1e6 as AMOUNT_PROVIDE_LIQUIDITY FROM terra.msgs
    WHERE MSG_VALUE:"execute_msg":"provide_liquidity" IS NOT NULL
    Run a query to Download Data