omer93olas staking update
Updated 2025-01-13
999
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
›
⌄
-- credits to flipside dashboard for inspiration https://flipsidecrypto.xyz/flipsideteam/olas-key-activity-metrics-pnPjda
WITH gnosis_balance AS (
SELECT trunc(block_timestamp,'week') as week,
COALESCE(SUM(CASE WHEN TO_ADDRESS = lower('0xa45E64d13A30a51b91ae0eb182e88a40e9b18eD8') THEN AMOUNT ELSE 0 END), 0) AS total_received_in_olas,
COALESCE(SUM(CASE WHEN FROM_ADDRESS = lower('0xa45E64d13A30a51b91ae0eb182e88a40e9b18eD8') THEN AMOUNT ELSE 0 END), 0) AS total_sent_in_olas,
COALESCE(SUM(CASE WHEN TO_ADDRESS = lower('0xa45E64d13A30a51b91ae0eb182e88a40e9b18eD8') THEN AMOUNT ELSE 0 END), 0)
- COALESCE(SUM(CASE WHEN FROM_ADDRESS = lower('0xa45E64d13A30a51b91ae0eb182e88a40e9b18eD8') THEN AMOUNT ELSE 0 END), 0) AS net_in_olas,
sum(net_in_olas) over (order by week) as balance_in_olas
FROM gnosis.core.ez_token_transfers
WHERE contract_address = lower('0xcE11e14225575945b8E6Dc0D4F2dD4C570f79d9f')
group by 1
),
optimism_balance AS (
SELECT trunc(block_timestamp,'week') as week,
COALESCE(SUM(CASE WHEN TO_ADDRESS = lower('0xBb7e1D6Cb6F243D6bdE81CE92a9f2aFF7Fbe7eac') THEN AMOUNT ELSE 0 END), 0) AS total_received_in_olas,
COALESCE(SUM(CASE WHEN FROM_ADDRESS = lower('0xBb7e1D6Cb6F243D6bdE81CE92a9f2aFF7Fbe7eac') THEN AMOUNT ELSE 0 END), 0) AS total_sent_in_olas,
COALESCE(SUM(CASE WHEN TO_ADDRESS = lower('0xBb7e1D6Cb6F243D6bdE81CE92a9f2aFF7Fbe7eac') THEN AMOUNT ELSE 0 END), 0)
- COALESCE(SUM(CASE WHEN FROM_ADDRESS = lower('0xBb7e1D6Cb6F243D6bdE81CE92a9f2aFF7Fbe7eac') THEN AMOUNT ELSE 0 END), 0) AS net_in_olas,
sum(net_in_olas) over (order by week) as balance_in_olas
FROM optimism.core.ez_token_transfers
WHERE contract_address = lower('0xFC2E6e6BCbd49ccf3A5f029c79984372DcBFE527')
group by 1
),
-- Expedition Stakers (Everest, Coastal, Alpine)
staker_expedition AS (
SELECT
BLOCK_TIMESTAMP,
tx_hash,
MULTISIG_ADDRESS AS multisig,
OWNER_ADDRESS AS owner,
SERVICE_ID AS serviceId,
ROW_NUMBER() OVER (
PARTITION BY SERVICE_ID, OWNER_ADDRESS
QueryRunArchived: QueryRun has been archived