# Spells

SELECT 
    SUM(amount_usd)/1e6 AS "Volume on Balancer"
    , 1 AS rn 
FROM balancer.trades 
WHERE block_time >= CAST(NOW() AS TIMESTAMP) - INTERVAL '1' DAY 

UNION ALL

SELECT 
    SUM(amount_usd)/1e6 AS "Volume on Balancer"
    , 2 AS rn 
FROM balancer.trades 
WHERE block_time >= CAST(NOW() AS TIMESTAMP) - INTERVAL '7' DAY

UNION ALL

SELECT 
    SUM(amount_usd)/1e6 AS "Volume on Balancer"
    , 3 AS rn 
FROM balancer.trades 
WHERE block_time >= CAST(NOW() AS TIMESTAMP) - INTERVAL '30' DAY
ORDER BY rn ASC

SELECT 
    block_date
    , tx_hash
    , project_contract_address AS pool_address
    , token_bought_address
    , token_bought_amount
    , token_sold_address
    , token_sold_amount
    , tx_from
    , tx_to
    , amount_usd
    , swap_fee
FROM balancer.trades
WHERE block_time >= now() - interval '24' hour
ORDER BY 1 ASC

SELECT 
    blockchain
    , CAST(day AS TIMESTAMP) AS day
    , sum(protocol_liquidity_usd) AS chain_tvl
FROM balancer.liquidity x
GROUP BY 1, 2
ORDER BY 2 DESC, 3 DESC

SELECT 
    blockchain
    , pool_id
    , pool_symbol
    , sum(pool_liquidity_usd) AS pool_tvl
FROM balancer.liquidity x
WHERE day >= current_date
GROUP BY 1, 2, 3
ORDER BY 4 DESC

WITH 
    swaps AS (
        SELECT
            date_trunc('day', d.block_time) AS day
            , SUM(amount_usd) AS volume
        FROM balancer.trades d
        )
        GROUP BY 1
    ),

    total_tvl AS (
        SELECT 
            CAST(day as timestamp) as day
            , SUM(protocol_liquidity_usd) AS tvl
        FROM balancer.liquidity
        )
        GROUP BY 1
    )
   
SELECT
    CAST(t.day as timestamp) as day,
    (s.volume)/(t.tvl) AS liquidity_utilization,
FROM total_tvl t
LEFT JOIN swaps s ON s.day = t.day
ORDER BY 1