select distinct

block_timestamp :: date as block_date_in,

label as in_label,

sum(value) as input_value

from bitcoin.core.fact_inputs i

inner join (select distinct label, address from bitcoin.core.dim_labels where label_type = 'cex') l

on lower(i.pubkey_script_address) = lower(l.address)

where block_timestamp > current_date - {{lookback_days}}

group by 1,2

),

output_dat as (

select distinct

block_timestamp :: date as block_date_out,

label as out_label,

sum(value) as output_value

from bitcoin.core.fact_outputs o

inner join (select distinct label, address from bitcoin.core.dim_labels where label_type = 'cex') l

on lower(o.pubkey_script_address) = lower(l.address)

where block_timestamp > current_date - {{lookback_days}}

group by 1,2

select

coalesce(block_date_in, block_date_out) as block_date,

coalesce(in_label, out_label) as label,

coalesce(input_value, 0) as input_value,

coalesce(output_value, 0) as output_value,

coalesce(input_value, 0) - coalesce(output_value, 0) as net_value

from input_dat i

left join output_dat o

on i.block_date_in = o.block_date_out and i.in_label = o.out_label