select

--*

date_trunc('day', block_timestamp::date) as date,

sum(case when mint = 'fueL3hBZjLLLJHiFH9cqZoozTG3XQZ53diwFPwbzNim'

then amount

end) AS fuel_harvest,

sum(case when mint = 'foodQJAztMzX1DKpLaiounNe2BDMds5RNuPC6jsNrDG'

then amount

end) AS food_harvest,

sum(case when mint = 'ammoK8AkX2wnebQb35cDAZtTkvsXQbi82cGeTnUvvfK'

then amount

end) AS ammo_harvest,

sum(case when mint = 'tooLsNYLiVqzg8o4m3L2Uetbn62mvMWRqkog6PQeYKL'

then amount

end) AS tool_harvest,

-----------------------------------------

avg(fuel_harvest) over (order by date rows between 6 preceding and current row) as avg_fuel_harvest,

avg(food_harvest) over (order by date rows between 6 preceding and current row) as avg_food_harvest,

avg(ammo_harvest) over (order by date rows between 6 preceding and current row) as avg_ammo_harvest,

avg(tool_harvest) over (order by date rows between 6 preceding and current row) as avg_tool_harvest,

-----------------------------------------

zeroifnull(avg_fuel_harvest) + zeroifnull(avg_food_harvest) + zeroifnull(avg_ammo_harvest) + zeroifnull(avg_tool_harvest) as avg_r4_harvest

from solana.core.fact_transfers

WHERE tx_from IN ('6gxMWRY4DJnx8WfJi45KqYY1LaqMGEHfX9YdLeQ6Wi5')

AND mint IN ('fueL3hBZjLLLJHiFH9cqZoozTG3XQZ53diwFPwbzNim', 'foodQJAztMzX1DKpLaiounNe2BDMds5RNuPC6jsNrDG', 'ammoK8AkX2wnebQb35cDAZtTkvsXQbi82cGeTnUvvfK', 'tooLsNYLiVqzg8o4m3L2Uetbn62mvMWRqkog6PQeYKL')

AND block_timestamp > '2024-04-04' --current_date-60 --'2023-01-01' AND block_timestamp < current_date

group by 1

),

r4_consumption_SB AS (

date_trunc('day', block_timestamp::date) as date,