Vaults

SELECT
    block_date,
    contract_address AS vault_address,
    COUNT(*) AS num_deposits,
    COUNT(DISTINCT sender) AS unique_depositors
FROM metamorpho_vaults_ethereum.metamorpho_evt_deposit
WHERE block_date >= CURRENT_DATE - INTERVAL '30' DAY
GROUP BY 1, 2
ORDER BY 1 DESC

SELECT
    'ethereum' AS chain,
    date_trunc('day', evt_block_time) AS day,
    COUNT(*) AS num_deposits
FROM euler_v2_ethereum.evault_evt_deposit
WHERE evt_block_time >= NOW() - INTERVAL '30' DAY
GROUP BY 1, 2

UNION ALL

SELECT
    'base' AS chain,
    date_trunc('day', evt_block_time) AS day,
    COUNT(*) AS num_deposits
FROM euler_v2_base.evault_evt_deposit
WHERE evt_block_time >= NOW() - INTERVAL '30' DAY
GROUP BY 1, 2

UNION ALL

SELECT
    'arbitrum' AS chain,
    date_trunc('day', evt_block_time) AS day,
    COUNT(*) AS num_deposits
FROM euler_v2_arbitrum.evault_evt_deposit
WHERE evt_block_time >= NOW() - INTERVAL '30' DAY
GROUP BY 1, 2

ORDER BY day DESC, chain

SELECT
    chain,
    COUNT(*) AS num_supplies,
    COUNT(DISTINCT "user") AS unique_suppliers
FROM aave_v3_multichain.pool_evt_supply
WHERE evt_block_time >= NOW() - INTERVAL '30' DAY
GROUP BY 1
ORDER BY num_supplies DESC

SELECT
    'T1' AS vault_type,
    date_trunc('day', evt_block_time) AS day,
    COUNT(*) AS num_operations
FROM fluid_multichain.fluidvaultt1_evt_logoperate
WHERE evt_block_time >= NOW() - INTERVAL '30' DAY
GROUP BY 1, 2

UNION ALL

SELECT
    'T2' AS vault_type,
    date_trunc('day', evt_block_time) AS day,
    COUNT(*) AS num_operations
FROM fluid_multichain.fluidvaultt2_evt_logoperate
WHERE evt_block_time >= NOW() - INTERVAL '30' DAY
GROUP BY 1, 2

UNION ALL

SELECT
    'T3' AS vault_type,
    date_trunc('day', evt_block_time) AS day,
    COUNT(*) AS num_operations
FROM fluid_multichain.fluidvaultt3_evt_logoperate
WHERE evt_block_time >= NOW() - INTERVAL '30' DAY
GROUP BY 1, 2

UNION ALL

SELECT
    'T4' AS vault_type,
    date_trunc('day', evt_block_time) AS day,
    COUNT(*) AS num_operations
FROM fluid_multichain.fluidvaultt4_evt_logoperate
WHERE evt_block_time >= NOW() - INTERVAL '30' DAY
GROUP BY 1, 2

ORDER BY day DESC, vault_type

SELECT
    date_trunc('day', call_block_time) AS day,
    COUNT(*) AS num_deposits,
    COUNT(DISTINCT tx_signer) AS unique_depositors
FROM kamino_vault_solana.kamino_vault_call_deposit
WHERE call_block_time >= NOW() - INTERVAL '30' DAY
    AND call_success = true
GROUP BY 1
ORDER BY 1 DESC

WITH deposits AS (
    SELECT
        date_trunc('day', evt_block_time) AS day,
        SUM(CAST(amount AS DOUBLE)) / 1e18 AS eth_deposited
    FROM lido_ethereum.steth_evt_submitted
    WHERE evt_block_time >= NOW() - INTERVAL '30' DAY
    GROUP BY 1
),
withdrawals AS (
    SELECT
        date_trunc('day', evt_block_time) AS day,
        SUM(CAST(amountOfStETH AS DOUBLE)) / 1e18 AS steth_withdrawal_requested
    FROM lido_ethereum.withdrawalqueueerc721_evt_withdrawalrequested
    WHERE evt_block_time >= NOW() - INTERVAL '30' DAY
    GROUP BY 1
)

SELECT
    COALESCE(d.day, w.day) AS day,
    COALESCE(d.eth_deposited, 0) AS eth_deposited,
    COALESCE(w.steth_withdrawal_requested, 0) AS steth_withdrawal_requested,
    COALESCE(d.eth_deposited, 0) - COALESCE(w.steth_withdrawal_requested, 0) AS net_flow
FROM deposits d
FULL OUTER JOIN withdrawals w ON d.day = w.day
ORDER BY day DESC

WITH morpho AS (
    SELECT date_trunc('day', evt_block_time) AS day, 'Morpho (MetaMorpho)' AS protocol, COUNT(*) AS deposit_events
    FROM metamorpho_vaults_multichain.metamorpho_evt_deposit
    WHERE evt_block_time >= NOW() - INTERVAL '30' DAY
    GROUP BY 1
),
aave AS (
    SELECT date_trunc('day', evt_block_time) AS day, 'Aave v3' AS protocol, COUNT(*) AS deposit_events
    FROM aave_v3_multichain.pool_evt_supply
    WHERE evt_block_time >= NOW() - INTERVAL '30' DAY
    GROUP BY 1
),
lido AS (
    SELECT date_trunc('day', evt_block_time) AS day, 'Lido' AS protocol, COUNT(*) AS deposit_events
    FROM lido_ethereum.steth_evt_submitted
    WHERE evt_block_time >= NOW() - INTERVAL '30' DAY
    GROUP BY 1
),
fluid AS (
    SELECT date_trunc('day', evt_block_time) AS day, 'Fluid' AS protocol, COUNT(*) AS deposit_events
    FROM fluid_multichain.fluidvaultt1_evt_logoperate
    WHERE evt_block_time >= NOW() - INTERVAL '30' DAY
    GROUP BY 1
),
euler AS (
    SELECT date_trunc('day', evt_block_time) AS day, 'Euler v2' AS protocol, COUNT(*) AS deposit_events
    FROM euler_v2_ethereum.evault_evt_deposit
    WHERE evt_block_time >= NOW() - INTERVAL '30' DAY
    GROUP BY 1
)

SELECT * FROM morpho
UNION ALL SELECT * FROM aave
UNION ALL SELECT * FROM lido
UNION ALL SELECT * FROM fluid
UNION ALL SELECT * FROM euler
ORDER BY day DESC, protocol