ethprices AS (SELECT date_trunc('day',recorded_at) as date, close as price from public.daily_candles WHERE asset_id = '1027' AND recorded_at >= getdate() - interval '12 months')

SELECT

actions.date,

actions.n_transactions,

fees,

fees * ethprices.price as fees_usd

FROM (

SELECT

date_trunc('week',block_timestamp) as date,

count(distinct tx_hash) as n_transactions,

sum(fee) as fees

public.ethereum_events eee

WHERE eee.block_timestamp >= getdate() - interval '12 month'

AND (eee.from IN (SELECT address from addys)

OR eee.to IN (SELECT address from addys)

OR eee.contract_address IN (SELECT address from addys))

GROUP BY 1

) actions

JOIN ethprices ON ethprices.date = actions.date

ORDER BY 1;