MariaDB 10.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


10.6.7-MariaDB


SELECT SUBSTRING_INDEX('NAME_01', '_', 1);


SUBSTRING_INDEX('NAME_01', '_', 1)


NAME


CREATE TABLE usertable ( id int, username varchar(10), status varchar(10) );

INSERT INTO usertable VALUES
  (1,  'John'  ,     'Active')
, (2,  'Mike'  ,     'Active')
, (3,  'Ann'   ,     'Deactive')
, (4,  'Leta'  ,     'Active')
, (5,  'Lena'  ,     'Active')
, (6,  'Lara'  ,     'Active')
, (7,  'Mitch' ,     'Active')
;

CREATE TABLE revenuetb ( subuser varchar(10), hour varchar(20), revenue int );

INSERT INTO revenuetb VALUES
  ('John_01'  ,      '2/26/2022 5:00'  ,  5)
, ('Mike_01'  ,      '2/26/2022 7:00'  ,  8)
, ('Mike_01'  ,      '2/26/2022 7:00'  ,  22)
, ('Leta_03'  ,      '2/26/2022 7:00'  ,  67)
, ('Leta_07'  ,      '2/26/2022 9:00'  ,  56)
, ('Mitch_07' ,      '2/26/2022 11:00' ,  34)
;
-- Original SQL

SELECT u.Username
     , COALESCE(SUM(Revenue), 0) AS TOTAL_USAGE
  FROM usertable u
  LEFT JOIN revenuetb e
    ON SUBSTRING_INDEX(e.subuser, '_', 1) = u.Username
   AND e.Hour BETWEEN '1' and '9' 
 WHERE u.STATUS='Active'    
 GROUP BY u.Username 
 ORDER BY u.ID
;


Username
TOTAL_USAGE


John
5

Mike
30

Leta
123

Lena
0

Lara
0

Mitch
34


-- Updated SQL

SELECT username, SUM(MAX_USAGE) AS TOTAL_USAGE
  FROM (
        SELECT u.Username, e.hour
             , COALESCE(MAX(Revenue), 0) AS MAX_USAGE
          FROM usertable u
          LEFT JOIN revenuetb e
            ON SUBSTRING_INDEX(e.subuser, '_', 1) = u.Username
           AND e.Hour BETWEEN '1' and '9' 
         WHERE u.STATUS='Active'    
         GROUP BY u.Username, e.hour
       ) AS derived
 GROUP BY Username
;


username
TOTAL_USAGE


John
5

Lara
0

Lena
0

Leta
123

Mike
22

Mitch
34