dan-smith2023-02-02 01:47 PM
Updated 2023-02-02
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
›
⌄
WITH
addliquidity AS (
SELECT
DECODED_LOG:provider AS provider
,SUM(DECODED_LOG:token_amounts[0] / 1e18) AS eth_add
,SUM(DECODED_LOG:token_amounts[1] / 1e18) AS frxETH_add
FROM ethereum.core.fact_decoded_event_logs
WHERE
(EVENT_NAME = 'AddLiquidity')
AND CONTRACT_ADDRESS = '0xa1f8a6807c402e4a15ef4eba36528a3fed24e577'
GROUP BY 1
),
removeliquidity AS (
SELECT
DECODED_LOG:provider AS provider
,-SUM(DECODED_LOG:token_amounts[0] / 1e18) AS eth_rem
,-SUM(DECODED_LOG:token_amounts[1] / 1e18) AS frxETH_rem
FROM ethereum.core.fact_decoded_event_logs
WHERE
(EVENT_NAME = 'RemoveLiquidity')
AND CONTRACT_ADDRESS = '0xa1f8a6807c402e4a15ef4eba36528a3fed24e577'
GROUP BY 1
),
currentliquidity AS (
SELECT
a.provider
,eth_add + COALESCE(eth_rem,0) AS eth
,frxETH_add + COALESCE(frxETH_rem,0) AS frxETH
FROM addliquidity a
LEFT JOIN removeliquidity r ON a.provider = r.provider
)
SELECT *
FROM currentliquidity
Run a query to Download Data