Fix `balancer.view_trades` Abstraction

Testing Ethereum

I tested that table counts are equal between the Dune v1 abstraction and the new Dune v2 spell.

I filtered on block_time and minute greater than or equal to 2022-04-20 to reduce the number of rows.

I modified the view_trades abstraction query to exclude balancer v1 dexs:

SELECT
    COUNT(*)
FROM (
    -- V2
    SELECT
        t.evt_block_time AS block_time,
        'Balancer' AS project,
        '2' AS version,
        'DEX' AS category,
        NULL::bytea AS trader_a, -- this relies on the outer query coalescing to tx."from"
        NULL::bytea AS trader_b,
        t."amountOut" AS token_a_amount_raw,
        t."amountIn" AS token_b_amount_raw,
        NULL::numeric AS usd_amount,
        t."tokenOut" AS token_a_address,
        t."tokenIn" AS token_b_address,
        t."poolId" AS exchange_contract_address,
        s."swapFeePercentage" / 1e18 AS swap_fee,
        t.evt_tx_hash AS tx_hash,
        NULL::integer[] AS trace_address,
        t.evt_index
    FROM
        balancer_v2."Vault_evt_Swap" t
    LEFT JOIN balancer_v2.view_pools_fees s ON s.contract_address = SUBSTRING(t."poolId" FROM 0 FOR 21)
        AND s.evt_block_time = (
            SELECT MAX(evt_block_time)
            FROM balancer_v2.view_pools_fees
            WHERE
                evt_block_time <= t.evt_block_time
                AND contract_address = SUBSTRING(t."poolId" FROM 0 FOR 21)
        )
) dexs
INNER JOIN ethereum.transactions tx ON dexs.tx_hash = tx.hash
    AND tx.block_time >= '2022-04-20'
LEFT JOIN erc20.tokens erc20a ON erc20a.contract_address = dexs.token_a_address
LEFT JOIN erc20.tokens erc20b ON erc20b.contract_address = dexs.token_b_address
LEFT JOIN prices.usd pa ON pa.minute = DATE_TRUNC('minute', dexs.block_time)
    AND pa.contract_address = dexs.token_a_address
    AND pa.minute >= '2022-04-20'
LEFT JOIN prices.usd pb ON pb.minute = DATE_TRUNC('minute', dexs.block_time)
    AND pb.contract_address = dexs.token_b_address
    AND pb.minute >= '2022-04-20'
WHERE dexs.block_time >= '2022-04-20'

Here is the new Dune v2 spell which excludes the new dexs WHERE filter for testing purposes:

WITH stuff AS (
    SELECT *
    FROM balancer_v2_ethereum.WeightedPool_evt_SwapFeePercentageChanged
    UNION ALL
    SELECT *
    FROM balancer_v2_ethereum.StablePool_evt_SwapFeePercentageChanged
    UNION ALL
    SELECT *
    FROM balancer_v2_ethereum.MetaStablePool_evt_SwapFeePercentageChanged
    UNION ALL
    SELECT *
    FROM balancer_v2_ethereum.LiquidityBootstrappingPool_evt_SwapFeePercentageChanged
    UNION ALL
    SELECT *
    FROM balancer_v2_ethereum.InvestmentPool_evt_SwapFeePercentageChanged
    UNION ALL
    SELECT *
    FROM balancer_v2_ethereum.AaveLinearPool_evt_SwapFeePercentageChanged
    UNION ALL
    SELECT *
    FROM balancer_v2_ethereum.StablePhantomPool_evt_SwapFeePercentageChanged
),
swap_fees AS (
    SELECT
        swap.evt_block_number,
        swap.evt_tx_hash,
        swap.evt_index,
        SUBSTRING(swap. `poolId`, 0, 42) AS contract_address,
    swap.evt_block_time,
    MAX(fees.evt_block_time) AS max_fee_evt_block_time
FROM
    balancer_v2_ethereum.Vault_evt_Swap swap
    LEFT JOIN stuff fees ON fees.contract_address = SUBSTRING(swap. `poolId`, 0, 42)
        AND fees.evt_block_time <= swap.evt_block_time
GROUP BY 1, 2, 3, 4, 5
),
dexs AS (
    SELECT
        swap.evt_block_time AS block_time,
        NULL AS taker,
        NULL AS maker,
        swap. `amountOut` AS token_bought_amount_raw,
        swap. `amountIn` AS token_sold_amount_raw,
        NULL AS amount_usd,
        swap. `tokenOut` AS token_bought_address,
        swap. `tokenIn` AS token_sold_address,
        swap. `poolId` AS project_contract_address,
        pools_fees. `swapFeePercentage` / POWER(10, 18) AS swap_fee,
        swap.evt_tx_hash AS tx_hash,
        NULL AS trace_address,
        swap.evt_index
    FROM swap_fees
    INNER JOIN balancer_v2_ethereum.Vault_evt_Swap swap ON swap.evt_block_number = swap_fees.evt_block_number
        AND swap.evt_tx_hash = swap_fees.evt_tx_hash
        AND swap.evt_index = swap_fees.evt_index
    LEFT JOIN stuff pools_fees ON pools_fees.contract_address = swap_fees.contract_address
        AND pools_fees.evt_block_time = swap_fees.max_fee_evt_block_time
)
SELECT
    COUNT(*)
FROM
    dexs
INNER JOIN ethereum.transactions tx ON tx.hash = dexs.tx_hash
    AND tx.block_time >= '2022-04-20'
LEFT JOIN tokens.erc20 erc20a ON erc20a.contract_address = dexs.token_bought_address
    AND erc20a.blockchain = 'ethereum'
LEFT JOIN tokens.erc20 erc20b ON erc20b.contract_address = dexs.token_sold_address
    AND erc20b.blockchain = 'ethereum'
LEFT JOIN prices.usd p_bought ON p_bought.minute = DATE_TRUNC('minute', dexs.block_time)
    AND p_bought.contract_address = dexs.token_bought_address
    AND p_bought.blockchain = 'ethereum'
    AND p_bought.minute >= '2022-04-20'
LEFT JOIN prices.usd p_sold ON p_sold.minute = DATE_TRUNC('minute', dexs.block_time)
    AND p_sold.contract_address = dexs.token_sold_address
    AND p_sold.blockchain = 'ethereum'
    AND p_sold.minute >= '2022-04-20'

balancer / dune-spellbook

Fix `balancer.view_trades` Abstraction #19

Testing Ethereum