SELECT

block_timestamp,

tx_id,

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

where block_timestamp > '2021-08-02'::date - interval '1 month' - interval '1 day'

and block_timestamp <= '2021-08-02'

),

fees_by_day as (

select

date_trunc('day', block_timestamp) as date,

case when dayname(date) in ('Sat', 'Sun') then 'Weekend' else 'Weekday' end as day_type,

dayname(date) as day_name,

count(tx_id) as tx_count,

sum(amount0_usd) + sum(amount1_usd) as fees,

lag(fees, 1, 0) over (order by date) as fees_lag1,

fees - fees_lag1 as delta_fees,

case when delta_fees >= 0 then delta_fees else 0 end as delta_pos,

case when delta_fees < 0 then delta_fees else 0 end as delta_neg

from collected_fees

where date > '2021-08-02'::date - interval '1 month'

group by 1

order by 1

),

aggregate_fees as (

avg(fees) as avg_fees,

stddev(fees) as stddev_fees

from fees_by_day