matejchiSyrup pools 30 day growth
Updated 2024-10-28
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
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
)