matejchiSyrup pools 30 day growth
    Updated 2024-10-28
    with all_pools as (
    select
    case
    when contract_address = lower('0x356B8d89c1e1239Cbbb9dE4815c39A1474d5BA7D') then 'Syrup USDT'
    when contract_address = lower('0x80ac24aA929eaF5013f6436cdA2a7ba190f5Cc0b') then 'Syrup USDC'
    end as pool,
    date_trunc('day', block_timestamp) as day,
    sum(case
    when event_name = 'Deposit' then decoded_log:assets_::int / 1e6
    when event_name = 'Withdraw' then - decoded_log:assets_::int / 1e6
    else 0 end) as daily_supply
    from ethereum.core.ez_decoded_event_logs
    where contract_address in (
    lower('0x356B8d89c1e1239Cbbb9dE4815c39A1474d5BA7D'), -- syrup_USDT
    lower('0x80ac24aA929eaF5013f6436cdA2a7ba190f5Cc0b') -- syrup_USDC
    )
    group by pool, day
    ),

    daily_balances as (
    select
    pool,
    day,
    sum(daily_supply) over (partition by pool order by day) as cumulative_balance
    from all_pools
    ),

    growth as (
    select
    pool,
    max(case when day = (select max(day) from daily_balances) then cumulative_balance end) as latest_balance,
    max(case when day = dateadd(day, -30, (select max(day) from daily_balances)) then cumulative_balance end) as balance_30_days_ago
    from daily_balances
    group by pool
    )