DAY | TOKEN0_BAL | TOKEN1_BAL | PRICE_0 | PRICE_1 | |
|---|---|---|---|---|---|
1 | 2024-02-07 00:00:00.000 | 5032452.00321054 | 5102321.02073336 | 9.255639329e-7 | 2369.907623956 |
2 | 2024-02-06 00:00:00.000 | 5007291.85082773 | 4923445.04447889 | 9.017626667e-7 | 2335.06375 |
3 | 2024-02-05 00:00:00.000 | 5015418.57282533 | 4901652.52002533 | 9.136146346e-7 | 2299.170416667 |
4 | 2024-02-04 00:00:00.000 | 4967222.10638982 | 4896507.30775021 | 9.051110417e-7 | 2297.095 |
5 | 2024-02-03 00:00:00.000 | 5032379.04835345 | 5001681.98818661 | 9.32342875e-7 | 2308.594583333 |
6 | 2024-02-02 00:00:00.000 | 5008196.39942993 | 4984268.06841292 | 9.274204167e-7 | 2302.232083333 |
7 | 2024-02-01 00:00:00.000 | 4965673.7294619 | 4896240.00780143 | 9.153660833e-7 | 2272.497916667 |
8 | 2024-01-31 00:00:00.000 | 5161325.58089614 | 4996385.80367377 | 9.539739583e-7 | 2313.784166667 |
9 | 2024-01-30 00:00:00.000 | 5473967.79198475 | 5209203.67459466 | 0.00000104875 | 2328.230416667 |
10 | 2024-01-29 00:00:00.000 | 5352306.27586724 | 5253480.46603925 | 0.000001060416667 | 2272.340416667 |
11 | 2024-01-28 00:00:00.000 | 5400309.19127015 | 5331699.4746084 | 0.00000108625 | 2272.091666667 |
12 | 2024-01-27 00:00:00.000 | 5362054.33058099 | 5375923.86994686 | 0.000001090416667 | 2266.472916667 |
13 | 2024-01-26 00:00:00.000 | 5245125.04922126 | 5272515.71341979 | 0.000001059583333 | 2237.15875 |
14 | 2024-01-25 00:00:00.000 | 5134407.5385947 | 5141912.79131249 | 0.000001024166667 | 2210.020416667 |
15 | 2024-01-24 00:00:00.000 | 5196140.72253334 | 5186118.37173349 | 0.0000010375 | 2227.209583333 |
16 | 2024-01-23 00:00:00.000 | 5173924.20328491 | 5249409.3795185 | 0.000001031816458 | 2257.564583333 |
17 | 2024-01-22 00:00:00.000 | 5534331.37774071 | 5503309.21847127 | 0.000001092916667 | 2391.14125 |
18 | 2024-01-21 00:00:00.000 | 5834346.18263174 | 5707671.0733965 | 0.000001155939519 | 2472.16125 |
19 | 2024-01-20 00:00:00.000 | 5596985.79034211 | 5801155.1642412 | 0.000001127205541 | 2472.411838445 |
20 | 2024-01-19 00:00:00.000 | 5689765.65867431 | 5697139.18098957 | 0.000001126090054 | 2471.49541956 |
KeyrockUniV2 -- usdc/weth TVL Overtime -- working version
Updated 2024-02-07
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
›
⌄
⌄
-- forked from UniV2 -- token1/token0 TVL Overtime (V3) @ https://flipsidecrypto.xyz/edit/queries/7f9d30a7-238c-4642-9398-866b2611e2d8
/* Extract and categorize transfers involving token0 (token0)
('0xb4e16d0168e52d35cacd2c6185b44281ec28c9dc') -- pool
Token0 ('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'), -- token0
Token1 ('0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48') -- token1
)
*/
WITH transfers AS (
SELECT
BLOCK_TIMESTAMP,
CASE
WHEN tr.contract_address = '{{token0_contract}}' -- token 0
AND tr.to_address = '{{pool_contract}}' THEN TRY_CAST(raw_amount_precise AS DOUBLE) -- pool addy
WHEN tr.contract_address = '{{token0_contract}}' -- token 0
AND tr.from_address = '{{pool_contract}}' THEN - TRY_CAST(raw_amount_precise AS DOUBLE) -- pooly addy
END AS token0
FROM
ethereum.core.fact_token_transfers AS tr
WHERE
(
tr.to_address = '{{pool_contract}}' -- pool addy
)
OR (
tr.from_address = '{{pool_contract}}' -- pool addy
)
AND tr.contract_address IN (
'{{token0_contract}}' -- token 1
)
),
-- Group and sum token0 transfers by date
grouped_transfers AS (
SELECT
DATE_TRUNC('day', BLOCK_TIMESTAMP) AS evt_date,
Last run: over 1 year ago
...
300
27KB
41s