TOTAL_DEPOSITS_USD | TOTAL_WITHDRAWALS_USD | NET_DEPOSITS_USD | |
|---|---|---|---|
1 | 54236640.77 | 4049891.43 | 50186749.33 |
elsinaTotal Deposits and Withdrwals in USD copy
Updated 2025-02-08
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
›
⌄
-- forked from Kruys-Collins / Total Deposits and Withdrwals in USD @ https://flipsidecrypto.xyz/Kruys-Collins/q/8_1fiWezjAIJ/total-deposits-and-withdrwals-in-usd
WITH BASE AS (
SELECT
block_timestamp,
tx_hash,
event_name,
contract_name,
COALESCE(decoded_log:wad::int/1e18, decoded_log:assets::int/1e18) as amount,
CASE
WHEN contract_name ilike '%ETH%' THEN 'WETH'
WHEN contract_name ilike '%USD%' THEN 'USDT'
ELSE 'OTHER'
END AS asset_type
FROM swell.core.ez_decoded_event_logs
WHERE event_name IN ('Deposit', 'Withdrawal') and block_timestamp::date >= '2025-01-01'
),
PRICES AS (
SELECT
date_trunc('day', hour) as day,
symbol,
avg(price) as price_usd
FROM ethereum.price.ez_prices_hourly
WHERE symbol = 'WETH'
GROUP BY 1, 2
),
VALUED_TRANSACTIONS AS (
SELECT
b.*,
COALESCE(b.amount * p.price_usd, b.amount) as amount_usd -- Assumes stablecoin 1:1 for non-ETH
FROM BASE b
LEFT JOIN PRICES p
ON date_trunc('day', b.block_timestamp) = p.day
AND b.asset_type = p.symbol
),
TOTALS AS (
Last run: about 2 months ago
1
38B
3s