connorhETH Use after ALCX-ETH Swaps
Updated 2021-11-11
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
›
⌄
-- ALCX SLP swap volume/fees: using the ethereum.events_emitted table
WITH swaps_hourly AS(
-- use the events_emitted table to parse swaps
-- example: https://etherscan.io/tx/0x76e3af1083e5bb2f3aa87b974dc3c3a81e7aa8eb4830c69b0456f1a8a4d00038#eventlog
SELECT tx_to_address AS swapper,
DATE_TRUNC('hour',block_timestamp) AS swap_date, contract_address AS pool_address,
SUM(event_inputs:amount1In) AS amountIn,
MIN(block_timestamp) AS first_swap
FROM ethereum.events_emitted
WHERE block_timestamp >= CURRENT_DATE - interval '4 months' AND block_timestamp <= CURRENT_DATE - interval '1 day'
AND contract_address = '0xc3f279090a47e80990fe3a9c30d24cb117ef91a8' -- WETH-ALCX SLP
AND event_name = 'Swap'
GROUP BY 1,2,3
HAVING amountin > 0
)
SELECT
CASE WHEN to_label IS NULL THEN 'Other Users' ELSE to_label END AS to_label,to_address,
symbol,SUM(amount) AS amount,SUM(amount_usd) AS amount_usd,COUNT(tx_id) AS N_TXN
FROM
ethereum.udm_events
WHERE contract_address = LOWER('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2') AND from_address IN (SELECT swapper FROM swaps_hourly)
AND block_timestamp >= CURRENT_DATE - interval '7 days'
GROUP BY 1,2,3
ORDER BY AMOUNT DESC
Run a query to Download Data