select

date_trunc('day', block_timestamp) as deposit_day

, block_number

, contract_address

, decoded_log:deposit::int as deposit_raw

, decoded_log:recipient as recipient

, decoded_log:sender as sender

, decoded_log:startTime::int as start_time_s

, decoded_log:stopTime::int as stop_time_s

, decoded_log:streamId::int as stream_id

, decoded_log:tokenAddress as token_address

, to_timestamp_ntz(start_time_s) as start_time

, to_timestamp_ntz(stop_time_s) as stop_time

, deposit_raw / (stop_time_s - start_time_s) as stream_rate

from ethereum.core.fact_decoded_event_logs

where 1=1

and contract_address in ('0x25f2226b597e8f9514b3f68f00f494cf4f286491','0x464c71f6c2f760dda6093dcb91c24c39e5d6e18c')

and block_timestamp > '2022-05-06' -- this is the date of the first stream

and event_name = 'CreateStream'

and block_number <= 17198486

)

, withdraw_streams as (

select

contract_address

, decoded_log:recipient as recipient

, decoded_log:streamId::int as stream_id

, sum(decoded_log:amount::int) as amount

from ethereum.core.fact_decoded_event_logs

where 1=1

and contract_address in ('0x25f2226b597e8f9514b3f68f00f494cf4f286491','0x464c71f6c2f760dda6093dcb91c24c39e5d6e18c')

and block_timestamp > '2022-05-06'

and event_name = 'WithdrawFromStream'

and block_number <= 17198486

group by 1,2,3