select date_trunc('day',block_timestamp) as date

, pool_address

, pool_name

, token0_symbol

, token1_symbol

, case when amount0_usd is null then 0 else amount0_usd end as amount0_usd

, case when amount1_usd is null then 0 else amount1_usd end as amount1_usd

from uniswapv3.position_collected_fees

),

token0s as (

select date

, pool_address

, pool_name

, token0_symbol as token

, sum(amount0_usd) as token_amount_fee

from collected_fees

group by 1,2,3,4

),

token1s as (

select date

, pool_address

, pool_name

, token1_symbol as token

, SUM(amount0_usd) as token_amount_earned

from collected_fees

group by 1,2,3,4

),

pool_fee as (

select * from token0s union

select * from token1s

),

pool_fee_total as (