SELECT *

FROM optimism.core.ez_eth_transfers a

LEFT JOIN optimism.core.dim_labels b

ON a.eth_from_address = b.address

WHERE date_trunc('day',block_timestamp) BETWEEN current_date -7 and CURRENT_DATE-1

AND label_type is not null),

target as (

SELECT *

FROM optimism.core.ez_eth_transfers a

LEFT JOIN optimism.core.dim_labels b

ON a.eth_to_address = b.address

WHERE date_trunc('day',block_timestamp) BETWEEN current_date -7 and current_date -1

AND label_type is not null)

SELECT eth_from_address as source,

t_eth_to_address as target,

sum(amount) as amnt

(

SELECT s.*, t.eth_to_address as t_eth_to_address, t.address as t_address, t.address_name as t_address_name, t.label_type as t_label_type, t.project_name as t_project_name, t.label_subtype as t_label_subtype

FROM source s

LEFT JOIN target t

ON t.tx_hash = s.tx_hash

WHERE s.label_type is not null and t_label_type is not null

GROUP BY source,target