53 rows yesterday at 4:21 PM
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
with
map(
'Ethereum', map(
unhex('59d9356e565ab3a36dd77763fc0d87feaf85508c'), ('0x59d9356e565ab3a36dd77763fc0d87feaf85508c', 'USDM', 18, 'Mountain', 'Ethereum'),
unhex('7712c34205737192402172409a8f7ccef8aa2aec'), ('0x7712c34205737192402172409a8f7ccef8aa2aec', 'BUIDL', 6, 'BlackRock', 'Ethereum'),
unhex('96f6ef951840721adbf46ac996b59e0235cb985c'), ('0x96f6ef951840721adbf46ac996b59e0235cb985c', 'USDY', 18, 'Ondo', 'Ethereum'),
unhex('e86845788d6e3e5c2393ade1a051ae617d974c09'), ('0xe86845788d6e3e5c2393ade1a051ae617d974c09', 'USDYc', 18, 'Ondo', 'Ethereum'),
unhex('136471a34f6ef19fe571effc1ca711fdb8e49f2b'), ('0x136471a34f6ef19fe571effc1ca711fdb8e49f2b', 'USYC', 6, 'Hashnote', 'Ethereum'),
unhex('1b19c19393e2d034d8ff31ff34c81252fcbbee92'), ('0x1b19c19393e2d034d8ff31ff34c81252fcbbee92', 'OUSG', 18, 'Ondo', 'Ethereum'),
unhex('43415eb6ff9db7e26a15b704e7a3edce97d31c4e'), ('0x43415eb6ff9db7e26a15b704e7a3edce97d31c4e', 'USTB', 6, 'Superstate', 'Ethereum'),
unhex('dd50c053c096cb04a3e3362e2b622529ec5f2e8a'), ('0xdd50c053c096cb04a3e3362e2b622529ec5f2e8a', 'TBILL', 6, 'OpenEden', 'Ethereum'),
unhex('e4880249745eac5f1ed9d8f7df844792d560e750'), ('0xe4880249745eac5f1ed9d8f7df844792d560e750', 'USTBL', 5, 'Spiko', 'Ethereum')
),
'Arbitrum', map(
unhex('59d9356e565ab3a36dd77763fc0d87feaf85508c'), ('0x59d9356e565ab3a36dd77763fc0d87feaf85508c', 'USDM', 18, 'Mountain', 'Arbitrum'),
unhex('b9e4765bce2609bc1949592059b17ea72fee6c6a'), ('0xb9e4765bce2609bc1949592059b17ea72fee6c6a', 'BENJI',18, 'Franklin','Arbitrum')
),
'Polygon', map(
unhex('59d9356e565ab3a36dd77763fc0d87feaf85508c'), ('0x59d9356e565ab3a36dd77763fc0d87feaf85508c', 'USDM', 18, 'Mountain', 'Polygon'),
unhex('408a634b8a8f0de729b48574a3a7ec3fe820b00a'), ('0x408a634b8a8f0de729b48574a3a7ec3fe820b00a', 'BENJI',18, 'Franklin','Polygon'),
unhex('e4880249745eac5f1ed9d8f7df844792d560e750'), ('0xe4880249745eac5f1ed9d8f7df844792d560e750', 'USTBL', 5, 'Spiko', 'Polygon')
),
'Base', map(
unhex('59d9356e565ab3a36dd77763fc0d87feaf85508c'), ('0x59d9356e565ab3a36dd77763fc0d87feaf85508c', 'USDM', 18, 'Mountain', 'Base')
)
) as tokenized_treasuries,
-- Subquery to extract data from each chain
ethereum_token_data as (
select
date_trunc('week', timestamp) as date,
t.token_address,
t.value,
tokenized_treasuries['Ethereum'][t.token_address].2 as symbol,
tokenized_treasuries['Ethereum'][t.token_address].3 as decimals,
tokenized_treasuries['Ethereum'][t.token_address].4 as entity,
tokenized_treasuries['Ethereum'][t.token_address].5 as chain
from token_total_supplies_ethereum_mainnet_v1 t
where t.token_address in mapKeys(tokenized_treasuries['Ethereum'])
and timestamp >= now() - interval '365 days'
),
arbitrum_token_data as (
select
date_trunc('week', timestamp) as date,
t.token_address,
t.value,
tokenized_treasuries['Arbitrum'][t.token_address].2 as symbol,
tokenized_treasuries['Arbitrum'][t.token_address].3 as decimals,
tokenized_treasuries['Arbitrum'][t.token_address].4 as entity,
tokenized_treasuries['Arbitrum'][t.token_address].5 as chain
from token_total_supplies_arbitrum_mainnet_v1 t
where t.token_address in mapKeys(tokenized_treasuries['Arbitrum'])
and timestamp >= now() - interval '365 days'
),
polygon_token_data as (
select
date_trunc('week', timestamp) as date,
t.token_address,
t.value,
tokenized_treasuries['Polygon'][t.token_address].2 as symbol,
tokenized_treasuries['Polygon'][t.token_address].3 as decimals,
tokenized_treasuries['Polygon'][t.token_address].4 as entity,
tokenized_treasuries['Polygon'][t.token_address].5 as chain
from token_total_supplies_polygon_mainnet_v1 t
where t.token_address in mapKeys(tokenized_treasuries['Polygon'])
and timestamp >= now() - interval '365 days'
),
base_token_data as (
select
date_trunc('week', timestamp) as date,
t.token_address,
t.value,
tokenized_treasuries['Base'][t.token_address].2 as symbol,
tokenized_treasuries['Base'][t.token_address].3 as decimals,
tokenized_treasuries['Base'][t.token_address].4 as entity,
tokenized_treasuries['Base'][t.token_address].5 as chain
from token_total_supplies_base_mainnet_v1 t
where t.token_address in mapKeys(tokenized_treasuries['Base'])
and timestamp >= now() - interval '365 days'
),
-- Combine all chain data with UNION ALL
all_token_data as (
select * from ethereum_token_data
union all
select * from arbitrum_token_data
union all
select * from polygon_token_data
union all
select * from base_token_data
),
-- Aggregate daily balances by date and entity
daily_entity_balances as (
select
date,
entity,
chain,
max(value / power(10, decimals)) as daily_balance
from all_token_data
group by date, entity, chain
)
-- Pivot daily balances by entity for charting
select
date as chart_x,
round(sum(case when entity = 'Mountain' then daily_balance else 0 end), 2) as chart_y_Mountain,
round(sum(case when entity = 'BlackRock' then daily_balance else 0 end), 2) as chart_y_BlackRock,
round(sum(case when entity = 'Ondo' then daily_balance * 1.06 else 0 end), 2) as chart_y_Ondo, -- simple multiplication. this should pull from the actual price
round(sum(case when entity = 'Hashnote' then daily_balance else 0 end), 2) as chart_y_Hashnote,
round(sum(case when entity = 'Superstate' then daily_balance else 0 end), 2) as chart_y_Superstate,
round(sum(case when entity = 'OpenEden' then daily_balance else 0 end), 2) as chart_y_OpenEden,
round(sum(case when entity = 'Spiko' then daily_balance else 0 end), 2) as chart_y_Spiko,
round(sum(case when entity = 'Franklin' then daily_balance else 0 end), 2) as chart_y_Franklin
from daily_entity_balances
group by date
order by date;