forked from tolian03/the-open-league
-
Notifications
You must be signed in to change notification settings - Fork 0
/
s6_part1_defi_tvl.sql
202 lines (200 loc) · 11 KB
/
s6_part1_defi_tvl.sql
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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
with jvault_pools as (
select address as pool_address from nft_items ni where collection_address =upper('0:184b700ed8d685af9fb0975094f103220b1acfd0e117627f368aa9ee493f452a')
), jvault_pool_tvls as (
select pool_address,
coalesce (sum( (select price_usd from prices.agg_prices ap where ap.base = jetton_master and price_time < 1726822800 order by price_time desc limit 1) * balance / 1e6), 0)
+
coalesce (sum( (select tvl_usd / total_supply from prices.dex_pool_history dph where pool = jetton_master and timestamp < 1726822800 order by timestamp desc limit 1) * balance), 0)
as value_usd
from tol.jetton_wallets_S6_end b
join jvault_pools p on p.pool_address = b."owner"
group by 1
), jvault_lp_tokens as (
select jm.address as lp_master, pool_address from jetton_masters jm join jvault_pools p on p.pool_address =admin_address
), jvault_balances_before as (
select ed.address, lp_master, balance from tol.jetton_wallets_s6_start b
join tol.enrollment_degen ed on ed.address = b."owner"
join jvault_lp_tokens on lp_master = b.jetton_master
), jvault_balances_after as (
select ed.address, lp_master, balance from tol.jetton_wallets_s6_end b
join tol.enrollment_degen ed on ed.address = b."owner"
join jvault_lp_tokens on lp_master = b.jetton_master
), jvault_balances_delta as (
select address, lp_master, coalesce(jvault_balances_after.balance, 0) - coalesce(jvault_balances_before.balance, 0) as balance_delta
from jvault_balances_after left join jvault_balances_before using(address, lp_master)
), jvault_total_supply as (
select lp_master, sum(balance) as total_supply
from tol.jetton_wallets_s6_end b
join jvault_lp_tokens on lp_master = b.jetton_master
group by 1
), jvault_impact as (
select address, sum(value_usd * balance_delta / total_supply) as tvl_impact from jvault_balances_delta
join jvault_total_supply using(lp_master)
join jvault_lp_tokens using(lp_master)
join jvault_pool_tvls using(pool_address)
group by 1
), settleton_pools as (
select address as pool_address from jetton_masters jm where
code_hash ='BfWQzLvuCKusWfxaQs48Xp+Nf+jUIBN8BVrU0li7qXI='
), settleton_pool_tvls as (
select pool_address,
coalesce (sum( (select tvl_usd / total_supply from prices.dex_pool_history dph where pool = jetton_master and timestamp < 1726822800 order by timestamp desc limit 1) * balance), 0)
as value_usd
from tol.jetton_wallets_S6_end b
join settleton_pools p on p.pool_address = b."owner"
group by 1
), settleton_balances_before as (
select ed.address, pool_address, balance from tol.jetton_wallets_s6_start b
join tol.enrollment_degen ed on ed.address = b."owner"
join settleton_pools on pool_address = b.jetton_master
), settleton_balances_after as (
select ed.address, pool_address, balance from tol.jetton_wallets_s6_end b
join tol.enrollment_degen ed on ed.address = b."owner"
join settleton_pools on pool_address = b.jetton_master
), settleton_balances_delta as (
select address, pool_address, coalesce(settleton_balances_after.balance, 0) - coalesce(settleton_balances_before.balance, 0) as balance_delta
from settleton_balances_after left join settleton_balances_before using(address, pool_address)
), settleton_total_supply as (
select pool_address, sum(balance) as total_supply
from tol.jetton_wallets_s6_end b
join settleton_pools on pool_address = b.jetton_master
group by 1
), settleton_impact as (
select address, sum(value_usd * balance_delta / total_supply) as tvl_impact from settleton_balances_delta
join settleton_total_supply using(pool_address)
join settleton_pool_tvls using(pool_address)
group by 1
), daolama_tvl as (
select balance * (select price from prices.ton_price where price_ts < 1726822800 order by price_ts desc limit 1) / 1e9 as tvl_usd
from account_states as2 where hash = (
select account_state_hash_after from transactions where account = upper('0:a4793bce49307006d3f4e97d815fb4c78ff7655faecf8606111ae29f8d6b41f4')
and now < 1726822800
order by now desc limit 1)
), daolama_balances_before as (
select ed.address, balance from tol.jetton_wallets_s6_start b
join tol.enrollment_degen ed on ed.address = b."owner"
where b.jetton_master = upper('0:a4793bce49307006d3f4e97d815fb4c78ff7655faecf8606111ae29f8d6b41f4')
), daolama_balances_after as (
select ed.address, balance from tol.jetton_wallets_s6_end b
join tol.enrollment_degen ed on ed.address = b."owner"
where b.jetton_master = upper('0:a4793bce49307006d3f4e97d815fb4c78ff7655faecf8606111ae29f8d6b41f4')
), daolama_balances_delta as (
select address, coalesce(daolama_balances_after.balance, 0) - coalesce(daolama_balances_before.balance, 0) as balance_delta
from daolama_balances_after left join daolama_balances_before using(address)
), daolama_total_supply as (
select sum(balance) as total_supply
from tol.jetton_wallets_s6_end b
where b.jetton_master = upper('0:a4793bce49307006d3f4e97d815fb4c78ff7655faecf8606111ae29f8d6b41f4')
), daolama_impact as (
select address, sum((select tvl_usd from daolama_tvl) * balance_delta / (select total_supply from daolama_total_supply)) as tvl_impact from daolama_balances_delta
group by 1
), tonhedge_tvl as (
select balance / 1e6 as tvl_usd from tol.jetton_wallets_s6_end
where owner = upper('0:57668d751f8c14ab76b3583a61a1486557bd746beeebbd4b2a65418b3fdb5471')
and jetton_master = '0:B113A994B5024A16719F69139328EB759596C38A25F59028B146FECDC3621DFE'
), tonhedge_balances_before as (
select ed.address, balance from tol.jetton_wallets_s6_start b
join tol.enrollment_degen ed on ed.address = b."owner"
where b.jetton_master = upper('0:57668d751f8c14ab76b3583a61a1486557bd746beeebbd4b2a65418b3fdb5471')
), tonhedge_balances_after as (
select ed.address, balance from tol.jetton_wallets_s6_end b
join tol.enrollment_degen ed on ed.address = b."owner"
where b.jetton_master = upper('0:57668d751f8c14ab76b3583a61a1486557bd746beeebbd4b2a65418b3fdb5471')
), tonhedge_balances_delta as (
select address, coalesce(tonhedge_balances_after.balance, 0) - coalesce(tonhedge_balances_before.balance, 0) as balance_delta
from tonhedge_balances_after left join tonhedge_balances_before using(address)
), tonhedge_total_supply as (
select sum(balance) as total_supply
from tol.jetton_wallets_s6_end b
where b.jetton_master = upper('0:57668d751f8c14ab76b3583a61a1486557bd746beeebbd4b2a65418b3fdb5471')
), tonhedge_impact as (
select address, sum((select tvl_usd from tonhedge_tvl) * balance_delta / (select total_supply from tonhedge_total_supply)) as tvl_impact
from tonhedge_balances_delta
group by 1
), tonpools_operations as (
select source as address, value / 1e9 *
(select price from prices.ton_price where price_ts < m.created_at order by price_ts desc limit 1) as value_usd
from messages m where direction ='in' and destination =upper('0:3bcbd42488fe31b57fc184ea58e3181594b33b2cf718500e108411e115978be1')
and created_at >= 1726138800 and created_at < 1726822800 and opcode = 569292295
union all
select m_in.source as address, -1 * m_out.value / 1e9 *
(select price from prices.ton_price where price_ts < m_out.created_at order by price_ts desc limit 1) as value_usd
from messages m_in
join messages m_out on m_out.tx_hash = m_in.tx_hash and m_out.direction = 'out'
join parsed.message_comments mc on mc.hash = m_out.body_hash
where m_in.direction ='in' and m_in.destination =upper('0:3bcbd42488fe31b57fc184ea58e3181594b33b2cf718500e108411e115978be1')
and m_in.created_at >= 1726138800 and m_in.created_at < 1726822800 and m_in.opcode = 195467089
and mc."comment" = 'Withdraw completed'
), tonpools_impact as (
select address, sum(value_usd) as tvl_impact
from tonpools_operations group by 1
), parraton_pools as (
select address as pool_address from jetton_masters jm where
admin_address = '0:705A574E176A47C785CCE821E5C1DC551BA65F70E828913EFAEF6DFA648184E6'
), parraton_pool_tvls as (
select pool_address,
coalesce (sum( (select tvl_usd / total_supply from prices.dex_pool_history dph where pool = jetton_master and timestamp < 1726822800 order by timestamp desc limit 1) * balance), 0)
as value_usd
from tol.jetton_wallets_S6_end b
join parraton_pools p on p.pool_address = b."owner"
group by 1
), parraton_balances_before as (
select ed.address, pool_address, balance from tol.jetton_wallets_s6_start b
join tol.enrollment_degen ed on ed.address = b."owner"
join parraton_pools on pool_address = b.jetton_master
), parraton_balances_after as (
select ed.address, pool_address, balance from tol.jetton_wallets_s6_end b
join tol.enrollment_degen ed on ed.address = b."owner"
join parraton_pools on pool_address = b.jetton_master
), parraton_balances_delta as (
select address, pool_address, coalesce(parraton_balances_after.balance, 0) - coalesce(parraton_balances_before.balance, 0) as balance_delta
from parraton_balances_after left join parraton_balances_before using(address, pool_address)
), parraton_total_supply as (
select pool_address, sum(balance) as total_supply
from tol.jetton_wallets_s6_end b
join parraton_pools on pool_address = b.jetton_master
group by 1
), parraton_impact as (
select address, sum(value_usd * balance_delta / total_supply) as tvl_impact from parraton_balances_delta
join parraton_total_supply using(pool_address)
join parraton_pool_tvls using(pool_address)
group by 1
), tonstable_flow as (
select
case when destination = upper('0:b606de2fc1c4a00b000194e7e097be466c6b82d06a515361ac64aaaa307bbe4f') then source
else destination end as address,
case when source = upper('0:b606de2fc1c4a00b000194e7e097be466c6b82d06a515361ac64aaaa307bbe4f') then -1 else 1 end * amount / 1e9 *
coalesce((select price from prices.core where asset = jetton_master_address and price_ts < tx_now order by price_ts desc limit 1), 1) *
(select price from prices.ton_price where price_ts < tx_now order by price_ts desc limit 1) as tvl_usd
from jetton_transfers
where (jetton_master_address = upper('0:cd872fa7c5816052acdf5332260443faec9aacc8c21cca4d92e7f47034d11892')
or jetton_master_address = upper('0:bdf3fa8098d129b54b4f73b5bac5d1e1fd91eb054169c3916dfc8ccd536d1000'))
and tx_now >= 1726138800 and tx_now < 1726822800
and (
destination = upper('0:b606de2fc1c4a00b000194e7e097be466c6b82d06a515361ac64aaaa307bbe4f')
or
source = upper('0:b606de2fc1c4a00b000194e7e097be466c6b82d06a515361ac64aaaa307bbe4f')
) and not tx_aborted
), tonstable_impact as (
select address, sum(tvl_usd) as tvl_impact from tonstable_flow
group by 1
), all_projects_impact as (
select 'jVault' as project, * from jvault_impact
union all
select 'SettleTon' as project, * from settleton_impact
union all
select 'DAOLama' as project, * from daolama_impact
union all
select 'TONHedge' as project, * from tonhedge_impact
union all
select 'TONPools' as project, * from tonpools_impact
union all
select 'Parraton' as project, * from parraton_impact
union all
select 'TONStable' as project, * from tonstable_impact
), all_projects_degen_only as (
select p.* from all_projects_impact p
join tol.enrollment_degen ed on ed.address = p.address
)
select address, sum(tvl_impact) as tvl_impact from all_projects_degen_only
group by 1