Union btw 2 table in a CTE (ETH)

angelnathUnion btw 2 table in a CTE (ETH)

Updated 2025-03-09

-- forked from Union btw 2 table in a CTE @ https://flipsidecrypto.xyz/studio/queries/3986a293-3f99-4df3-afa7-c157ba5867e8

-- and ethereum.defi.ez_dex_swaps

-- Doing same using ethereum ecosystem

--Find the top 10 most active address

-- using base.defi.ez_dex_swaps and base.core.dim_labels

-- Find the top 10 most active address

-- calcualte incoming and outgoing volumes

-- Use CTE and join together

WITH address_activity AS (

-- First part (outgoing txns)

SELECT

--block_timestamp as date,

origin_from_address as address,

SUM(amount_out_usd) as outgoing_usd,

0 as incoming_usd,

count(*) as tx_count,

FROM

ethereum.defi.ez_dex_swaps

WHERE

block_timestamp >= dateadd(day, -7, current_timestamp())

and amount_out_usd is not null

Group by 1

UNION ALL -- (combines the result of the two table)

-- Second part (incoming txns)

SELECT

--block_timestamp as date,

origin_to_address as address,

0 as outgoing_usd,

SUM(amount_in_usd) as incoming_usd,

count(*) as tx_count,

Last run: about 2 months ago

	ADDRESS	LABEL	TOTAL_TXN	TOTAL_INCOMING_USD	TOTAL_OUTGOING_USD
1	0x66a9893cc07d91d95644aedd05d03f95e1dba8af	token	208208	565590927.41	0
2	0x51c72848c68a965f66fa7a88855f9f7784502a7f	-	99320	2513577615.4	0
3	0x68d3a973e7272eb388022a5c6518d9b2a2e66fbf	-	84595	1311766107.15	0
4	0xa69babef1ca67a37ffaf7a485dfff3382056e78c	-	69000	881168877.75	0
5	0x0000000000001ff3684f28c67538d4d072c22734	-	66609	85793771.54	0
6	0x1111111254eeb25477b68fb85ed929f73a960582	dex	63914	68223989.5	0
7	0x7a250d5630b4cf539739df2c5dacb4c659f2488d	dex	57566	58934866.46	0
8	0x1f2f10d1c40777ae1da742455c65828ff36df387	-	53662	703781866.99	0
9	0xae2fc483527b8ef99eb5d9b44875f005ba1fae13	-	53621	0	702569067.3
10	0x881d40237659c251811cec9c364ef91dc08d300c	dex	53245	50992674.34	0
11	0x1ef032a3c471a99cc31578c8007f256d95e89896	-	50243	106465352.73	0
12	0x00000000009e50a7ddb7a7b0e2ee6604fd120e49	-	49926	129086205.78	0
13	0x93793bd1f3e35a0efd098c30e486a860a0ef7551	-	44823	0	709114404.02
14	0x111111125421ca6dc452d289314280a0f8842a65	dex	40894	226777508.94	0
15	0x9008d19f58aabd9ed0d60971565aa8510560ab41	dex	37679	524690430.07	0
16	0x3328f7f4a1d1c57c35df56bbf0c9dcafca309c49	-	32695	23781264.68	0
17	0xfc9928f6590d853752824b0b403a6ae36785e535	-	27414	0	71522490.78
18	0xe6b1de575e7e610889ea21024834e120f92033a3	-	27291	148104667.16	0
19	0xeff6cb8b614999d130e537751ee99724d01aa167	token	25424	225517987.86	0
20	0xe75ed6f453c602bd696ce27af11565edc9b46b0d	-	24951	0	83499276.43

...

1000

67KB