set @curdate = '2023-06-08 ';
 select DATE,ACCOUNT,
        CASE WHEN DATE = @curdate THEN VALUE 
             WHEN VALUE = 0 AND DATE = @curdate THEN MAX(VALUE)
             ELSE 0
        END AS MAX_VALUE
from table ;

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ACCOUNT ORDER BY DATE DESC) rn
    FROM yourTable
    WHERE VALUE <> 0
)
SELECT DATE, ACCOUNT, VALUE
FROM cte
WHERE rn = 1;

SELECT t1.DATE, t1.ACCOUNT, t1.VALUE
FROM yourTable t1
INNER JOIN
(
    SELECT ACCOUNT, MAX(DATE) AS MAX_DATE
    FROM yourTable
    WHERE VALUE <> 0
    GROUP BY ACCOUNT
) t2
    ON t2.ACCOUNT = t1.ACCOUNT AND
       t2.MAX_DATE = t1.DATE
WHERE
    t1.VALUE <> 0;

set @curdate = '2023-06-08';
with cte as (
  select ACCOUNT, DATE, max(VALUE) as max_value
  from mytable
  where DATE = @curdate
  group by ACCOUNT, DATE
)
select ACCOUNT, DATE, max_value as VALUE
from cte where max_value > 0
union all
select t.ACCOUNT, t.DATE, t.VALUE
from mytable t
inner join (
  select t.ACCOUNT, max(t.VALUE) as VALUE
  from cte c
  inner join mytable t on t.ACCOUNT = c.ACCOUNT
  where max_value = 0
  group by t.ACCOUNT
) as s on s.ACCOUNT = t.ACCOUNT and s.VALUE = t.VALUE

set @curdate = '2023-06-08';
select max(DATE) as 'DATE',
       ACCOUNT,
       coalesce(nullif(max(case when DATE = @curdate then VALUE end), 0), 
                (select value from table_name d 
                 where d.ACCOUNT = t.ACCOUNT and
                       d.DATE < @curdate and
                       d. value > 0
                 order by d.DATE DESC
                 limit 1)
               ) MAX_VALUE
from table_name t
WHERE DATE <= @curdate and value > 0
group by ACCOUNT

set @curdate = '2023-06-08';
set @rn =0;
set @acc = null;
select DATE, ACCOUNT, value 
  from
(
  select *,
     if(@acc<>ACCOUNT, @rn:=1, @rn:=@rn+1) rn,
     @acc:=ACCOUNT 
  from table_name 
  where DATE <= @curdate and value > 0
  order by ACCOUNT, DATE desc
) t
where rn = 1