jackguy2023-10-28 11:42 PM
Updated 2023-10-29
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 Uniswap Fe - 4 @ https://flipsidecrypto.xyz/edit/queries/03cc5b15-6ac6-4b47-ba1e-aeb47a2c76a0
with tab1 as (
SELECT
tx_hash
FROM ethereum.core.ez_token_transfers
WHERE to_address LIKE lower('0x37a8f295612602f2774d331e562be9e61B83a327')
and tx_hash in (
SELECT tx_hash
FROM ethereum.uniswapv3.ez_swaps
ORDER by block_timestamp DESC
) AND block_timestamp >= '2023-10-17'
UNION all
SELECT
tx_hash
from ethereum.core.ez_eth_transfers
WHERE eth_to_address LIKE lower('0x37a8f295612602f2774d331e562be9e61B83a327')
and tx_hash in (
SELECT tx_hash
FROM ethereum.uniswapv3.ez_swaps
ORDER by block_timestamp DESC
) AND block_timestamp >= '2023-10-17'
)
SELECT
CASE when tx_hash in (SELECT tx_hash from tab1) then 'used frontend' else 'did not use frontend' end as tx_type,
avg(ABS(AMOUNT0_USD)),
Median(ABS(AMOUNT0_USD))
FROM ethereum.uniswapv3.ez_swaps
where pool_address in (SELECT DISTINCT POOL_ADDRESS from ethereum.uniswapv3.ez_swaps where tx_hash in (SELECT tx_hash from tab1))
and block_timestamp > '2023-10-17'
--AND CASE when tx_hash in (SELECT tx_hash from tab1) then 'used frontend' else 'did not use frontend' end = 'did not use frontend'
GROUP BY 1
Run a query to Download Data