WITH bridges AS (

SELECT DISTINCT

value[0]::text AS bridge_address,

value[1]::text AS platform,

value[2]::text AS category

FROM (

SELECT livequery.live.udf_api('https://flipsidecrypto.xyz/api/queries/ca598443-7f38-482d-b0ae-eb106f09e229/latest-run') as response

), lateral FLATTEN (input => response:data:result:rows)

-- calculates the daily outflow amount of the wstETH from / to the bridges

, transfers as (

SELECT

BLOCK_TIMESTAMP::date AS time,

CASE

WHEN TO_ADDRESS IN (SELECT bridge_address FROM bridges WHERE (category = 'L2bridge' AND {{bridge_type}} = 'only L2 bridges')

OR (category = 'bridge' AND {{bridge_type}} = 'non-L2 bridges')

OR (category LIKE '%bridge%' AND {{bridge_type}} = 'all bridges')) THEN TO_ADDRESS

WHEN FROM_ADDRESS IN (SELECT bridge_address FROM bridges WHERE (category = 'L2bridge' AND {{bridge_type}} = 'only L2 bridges')

OR (category = 'bridge' AND {{bridge_type}} = 'non-L2 bridges')

OR (category LIKE '%bridge%' AND {{bridge_type}} = 'all bridges')) THEN FROM_ADDRESS

END AS bridge,

SUM(CASE WHEN TO_ADDRESS IN (SELECT bridge_address FROM bridges WHERE (category = 'L2bridge' AND {{bridge_type}} = 'only L2 bridges')

OR (category = 'bridge' AND {{bridge_type}} = 'non-L2 bridges')

OR (category LIKE '%bridge%' AND {{bridge_type}} = 'all bridges')) THEN AMOUNT ELSE 0 END) AS amount_in,

SUM(CASE WHEN FROM_ADDRESS IN (SELECT bridge_address FROM bridges WHERE (category = 'L2bridge' AND {{bridge_type}} = 'only L2 bridges')

OR (category = 'bridge' AND {{bridge_type}} = 'non-L2 bridges')

OR (category LIKE '%bridge%' AND {{bridge_type}} = 'all bridges')) THEN AMOUNT ELSE 0 END) AS amount_out

FROM ethereum.core.ez_token_transfers

WHERE (TO_ADDRESS IN (SELECT bridge_address FROM bridges WHERE (category = 'L2bridge' AND {{bridge_type}} = 'only L2 bridges')

OR (category = 'bridge' AND {{bridge_type}} = 'non-L2 bridges')

OR (category LIKE '%bridge%' AND {{bridge_type}} = 'all bridges'))

OR FROM_ADDRESS IN (SELECT bridge_address FROM bridges WHERE (category = 'L2bridge' AND {{bridge_type}} = 'only L2 bridges')

OR (category = 'bridge' AND {{bridge_type}} = 'non-L2 bridges')

OR (category LIKE '%bridge%' AND {{bridge_type}} = 'all bridges')))