SELECT

date_trunc('day',block_timestamp) as days,

liquidity_provider,

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

days,

liquidity_provider,

token0_symbol as token,

-- p.price as pr,

-- sum(amount0_adjusted) * pr as token_amount_earned

SUM(amount0_usd) AS token_amount_earned

FROM collected_fees

-- JOIN ethereum.token_prices_hourly p

-- ON f.token0_symbol = p.symbol

-- AND date_trunc('hour',f.block_timestamp) = p.hour

GROUP BY 1,2,3

),

token1s as (

SELECT

days,

liquidity_provider,

token1_symbol as token,

-- p.price as pr,

-- sum(amount1_adjusted) * pr as token_amount_earned

SUM(amount0_usd) AS token_amount_earned