SELECT

tx_hash as tx1,

CASE WHEN to_a = 1 and txns = 1 THEN 'Bridge from OP'

WHEN from_a = 1 and txns = 1 THEN 'Bridge to OP'

WHEN from_a = 1 and txns = 2 THEN 'Withdraw Liquidity'

WHEN to_a = 1 and txns = 2 THEN 'Deposit Liquidity' end as event

FROM (

SELECT

tx_hash,

COUNT(CASE WHEN to_address LIKE lower('0xd22363e3762cA7339569F3d33EADe20127D5F98C') then 1 END) as to_a,

COUNT(CASE WHEN from_address LIKE lower('0xd22363e3762cA7339569F3d33EADe20127D5F98C') then 1 END) as from_a,

count(*) as txns

FROM optimism.core.fact_token_transfers

WHERE tx_hash in (

SELECT

tx_hash

FROM optimism.core.fact_token_transfers

WHERE to_address LIKE lower('0xd22363e3762cA7339569F3d33EADe20127D5F98C')

OR from_address LIKE lower('0xd22363e3762cA7339569F3d33EADe20127D5F98C')

GROUP BY 1

)

GROUP BY 1

)

), tab1 as (

SELECT

date_trunc('day', block_timestamp) as day,

sum(CASE WHEN event LIKE 'Bridge from OP' THEN raw_amount / power(10, 6) END) as Bridge_from_OP,

sum(CASE WHEN event LIKE 'Bridge to OP' THEN raw_amount / power(10, 6) END) as Bridge_to_OP,

sum(CASE WHEN event LIKE 'Withdraw Liquidity' THEN raw_amount / power(10, 6) END) as Withdraw_Liquidity,

sum(CASE WHEN event LIKE 'Deposit Liquidity' THEN raw_amount / power(10, 6) END) as Deposit_Liquidity

FROM optimism.core.fact_token_transfers

LEFT outer JOIN tx_labels

on tx1 = tx_hash

WHERE not tx1 is NULL