SELECT to_date(block_timestamp) as date,

sum(CASE WHEN final.label_to_address = 'cex' THEN final.raw_amount ELSE 0 END)/pow(10,18) as op_transferred_out,

avg(CASE WHEN final.label_to_address = 'cex' THEN final.raw_amount END)/pow(10,18) as average_op_transferred_out,

median(CASE WHEN final.label_to_address = 'cex' THEN final.raw_amount END)/pow(10,18) as median_op_transferred_out,

max(CASE WHEN final.label_to_address = 'cex' THEN final.raw_amount END)/pow(10,18) as maximum_op_transferred_out,

min(CASE WHEN final.label_to_address = 'cex' THEN final.raw_amount END)/pow(10,18) as minimum_op_transferred_out,

sum(CASE WHEN final.from_address_label = 'cex' THEN final.raw_amount ELSE 0 END)/pow(10,18) as op_transferred_in,

avg(CASE WHEN final.from_address_label = 'cex' THEN final.raw_amount END)/pow(10,18) as average_op_transferred_in,

median(CASE WHEN final.from_address_label = 'cex' THEN final.raw_amount END)/pow(10,18) as median_op_transferred_in,

max(CASE WHEN final.from_address_label = 'cex' THEN final.raw_amount END)/pow(10,18) as maximum_op_transferred_in,

min(CASE WHEN final.from_address_label = 'cex' THEN final.raw_amount END)/pow(10,18) as minimum_op_transferred_in,

op_transferred_in-op_transferred_out as netflow_of_op

FROM (SELECT transfers.*, labels.label_type as from_address_label, labels.label_subtype as label_subtype_from, labels.project_name as project_name_from,

label.label_type as label_to_address, label.label_subtype as label_subtype_to, label.project_name as project_name_to

FROM optimism.core.fact_token_transfers transfers

LEFT JOIN Optimism.core.dim_labels labels

ON transfers.from_address = labels.address

LEFT JOIN Optimism.core.dim_labels label

ON transfers.to_address = label.address

WHERE contract_address = '0x4200000000000000000000000000000000000042') final

WHERE date between '2022-08-24' AND CURRENT_DATE-1

GROUP BY DATE

ORDER BY DATE DESC

select * from final_result