у тебя select account_id, max(date), balance ..
а группировка только по account_id. (с такой группировкой по одному account_id надо к balance надо применить какую то агр функцию, например max(), тогда запустится, но результат будет не такой как ты хочешь =) )
вот так сделай:
select
ad.*,
ab.balance
from
(
select
account_id, max(date) as max_date
from tmp_account_balance
group by account_id
) ad
inner join tmp_account_balance ab on ad.account_id = ab.account_id and ab.date = ad.max_date;
