Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
CREATE TABLE CUSTOMERS (
CUSTOMER_ID, FIRST_NAME, LAST_NAME,IS_ACTIVE) AS  SELECT 'L382059', 'Melanie', 'Zanzona','Y' FROM DUAL UNION ALL
SELECT 'P382319', 'Wayne', 'Zahn','Y' FROM DUAL UNION ALL 
SELECT 'E379466', 'Bonnie', 'Winterbottom','Y' FROM DUAL  UNION ALL 
SELECT 'X060162', 'Lisa','Saladino','Y'  FROM DUAL UNION ALL 
SELECT 'Y331964', 'Bella', 'Oxmix','Y' FROM DUAL  UNION ALL 
SELECT 'Z888555', 'Debra', 'Doss','Y' FROM DUAL;
6 rows affected

CREATE TABLE CUSTOMER_ACCOUNTS (
ACCOUNT_NUMBER,
CUSTOMER_ID, VENDOR_ID,
IS_ACTIVE) AS 
SELECT '0HE53T6WP9RV28P', 'Y331964', 1, 'Y' FROM DUAL 
 UNION ALL
SELECT 'Z17ARWYYZRCU2Q2', 'P382319', 1, 'Y' FROM DUAL 
 UNION ALL
 SELECT '0T81Z07CS6LXQ7Z', 'P382319', 3, 'Y' FROM DUAL 
UNION ALL 
SELECT 'YWYXC3Q5N9XZ7S', 'L382059', 1, 'Y' FROM DUAL UNION ALL
SELECT '612ZKAQ66VA3W3', 'Y331964', 3, 'Y' FROM DUAL    UNION ALL 
SELECT 'BCHD9TW78W67S1D', 'Z888555', 3, 'Y' FROM DUAL UNION ALL 
SELECT '0HLS87LDR1TE8WB', 
'X060162', 3, 'Y' FROM DUAL  UNION ALL 
SELECT 'Z69AG7DKS37UYU',
'X060162', 3, 'Y' FROM DUAL  UNION ALL 
SELECT 'B17ARWYYZRCU2Q2', 
'X060162', 3, 'Y' FROM DUAL  UNION ALL 
SELECT 'THVQD6M9LR7AVK', 'E379466', 1, 'Y' FROM DUAL  UNION ALL 
SELECT '0Z76WT5NTLRZPTW',
'E379466', 1, 'Y' FROM DUAL;
11 rows affected

create table transactions (
     transaction_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    account_number VARCHAR2(15),
    transaction_type varchar2(1) DEFAULT 'C',
    transaction_amount NUMBER(10,2),
     transaction_date DATE DEFAULT SYSDATE 
);
insert  into transactions(
account_number, transaction_type, transaction_amount, transaction_date)
SELECT '0HE53T6WP9RV28P', 'D', (LEVEL * 100.05), (DATE '2023-08-23' + 19/24) + (LEVEL-1) * (1/1440 * -5)  from dual  connect by level <= 15 union all
SELECT '0HE53T6WP9RV28P', 'C', (LEVEL * 50.00), (DATE '2023-08-23' + 19/24) + (LEVEL-1) * (1/1440 * -6)  from dual  connect by level <= 14 UNION ALL 
SELECT 'Z17ARWYYZRCU2Q2', 'D', (LEVEL * 1250.50), date '2023-05-14' + level * interval '5 15:13' day to minute from dual
          connect by level <= 7
union all
SELECT 'Z17ARWYYZRCU2Q2', 'C', (LEVEL * 1175.75), date '2023-07-04' + level * interval '1 21:23' day to minute from dual
          connect by level <= 5
union all
SELECT '0T81Z07CS6LXQ7Z', 'D', (LEVEL * 1250.50), date '2023-02-14' + level * interval '3 15:13' day to minute from dual
          connect by level <= 17
union all
SELECT '0T81Z07CS6LXQ7Z', 'C', (LEVEL * 75.75), date '2023-02-04' + level * interval '2 21:23' day to minute from dual
          connect by level <= 11
union all
select '612ZKAQ66VA3W3', 'D', 555.25 * LEVEL, (DATE '2023-07-13' + 13/24) + (level * 2)  from dual 
    connect by level <= 25 
UNION ALL 
select '612ZKAQ66VA3W3', 'C', 555.25 * LEVEL, (DATE '2023-07-23' + 13/24) + (level * 2)  from dual 
    connect by level <= 20 
UNION ALL 
select 'BCHD9TW78W67S1D', 'D', 1125.25 * LEVEL, date '2023-01-23' + level * interval '1 1' day to hour from dual
          connect by level <= 11
union all
select 'BCHD9TW78W67S1D', 'C', 925.00 * LEVEL, date '2023-01-25' + level * interval '1 1' day to hour from dual
          connect by level <= 7
union all
SELECT '0HLS87LDR1TE8WB', 'D', (LEVEL * 1250.50), date '2023-05-04' + level * interval '1 15:13' day to minute from dual
          connect by level <= 7
union all
SELECT '0HLS87LDR1TE8WB', 'C', (LEVEL * 1175.75), date '2023-05-04' + level * interval '1 15:13' day to minute from dual
          connect by level <= 5
union all
SELECT 'Z69AG7DKS37UYU', 'D', ((LEVEL * 5) * 1750), date '2023-06-01' + level * interval '1 18:43:35' day to second from dual
          connect by level <= 15
union all
SELECT 'Z69AG7DKS37UYU', 'C', ((LEVEL * 5) * 1750), date '2023-06-11' + level * interval '1 15:23:49' day to second from dual
          connect by level <= 13
union all
SELECT '0Z76WT5NTLRZPTW', 'D', (LEVEL * 100.57), date '2023-04-02' + level * interval '1 4' day to hour from dual
          connect by level <= 5
union all
SELECT '0Z76WT5NTLRZPTW', 'C', (LEVEL * 25.26), date '2023-04-04' + level * interval '1 4' day to hour from dual
          connect by level <= 5
union all
SELECT 'THVQD6M9LR7AVK', 'D', (LEVEL * 250.10), date '2023-05-10' + level * interval '1 7' day to hour from dual
          connect by level <= 13
union all
SELECT 'THVQD6M9LR7AVK', 'C', (LEVEL * 133.11), (SYSDATE - LEVEL)  from dual
          connect by level <= 9;
204 rows affected

create or replace function get_customer_balance (
  i_customer_id in varchar2
) return varchar2 sql_macro(scalar)
is
begin
  return q'[
   SELECT 
     COALESCE(SUM 
     (
       CASE  t.transaction_type
       WHEN  'C'
       THEN -t.transaction_amount
       ELSE t.transaction_amount
       END 
      )
   , 0)
   FROM  customer_accounts  ca
   JOIN  transactions       t  ON  t.account_number  = ca.account_number
   WHERE  ca.customer_id  = i_customer_id  -- one customer
   OR   ca.customer_id  IS NULL    ]';
end;
/
SELECT
    c.customer_id,
    c.first_name,
    c.last_name,
    get_customer_balance(c.customer_id) AS balance
FROM customers c 
ORDER BY balance DESC;


CUSTOMER_ID
FIRST_NAME
LAST_NAME
BALANCE


X060162
Lisa
Saladino
271127.75

P382319
Wayne
Zahn
203704.75

Y331964
Bella
Oxmix
70609.75

Z888555
Debra
Doss
48366.5

E379466
Bonnie
Winterbottom
17898.8

L382059
Melanie
Zanzona
0
CUSTOMER_ID	FIRST_NAME	LAST_NAME	BALANCE
X060162	Lisa	Saladino	271127.75
P382319	Wayne	Zahn	203704.75
Y331964	Bella	Oxmix	70609.75
Z888555	Debra	Doss	48366.5
E379466	Bonnie	Winterbottom	17898.8
L382059	Melanie	Zanzona	0