binhachonCowSwap - Market Mover? - Users
Updated 2022-04-21
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 CoW_transactions as (
select
distinct block_timestamp,
'CoW' as platform,
tx_hash
from ethereum_core.fact_event_logs
where contract_address = '0x9008d19f58aabd9ed0d60971565aa8510560ab41'
),
CoW_users as (
select
distinct from_address
from ethereum_core.fact_token_transfers
where to_address = '0x9008d19f58aabd9ed0d60971565aa8510560ab41'
and from_address not in (select address from ethereum_core.dim_contracts) --Remove contracts
),
-- Because the dex swaps table usually shows the router address instead of the user address, I add this table to cover that case
dex_users as (
select
distinct
platform,
tx_id,
ethereum_core.fact_transactions.from_address
from ethereum.dex_swaps
inner join ethereum_core.fact_transactions on (tx_id = tx_hash)
),
-- Search from both tables and use union to remove duplicates to cover both cases
CoW_users_platforms as (
select
distinct from_address,
platform
from ethereum.dex_swaps
where from_address in (select from_address from CoW_users)
and tx_id not in (select tx_hash from CoW_transactions)
union
select
distinct from_address,
Run a query to Download Data