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', 'Leo', 'Langford','Y' FROM DUAL UNION ALL
 SELECT 'P382319', 'Tom', 'Micelli','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', 'Sandy', 'Herring','Y' FROM DUAL  UNION ALL 
SELECT 'Z888555', 'Barbara', 'Broadwater','Y' FROM DUAL;

ALTER TABLE customers 
ADD CONSTRAINT customers_pk PRIMARY KEY (customer_id);

CREATE TABLE vendors AS
    SELECT level AS vendor_id,
       'Vendor ' || level AS vendor_name
    FROM   dual
    CONNECT BY level <= 3;

ALTER TABLE vendors 
    ADD ( CONSTRAINT vendors_pk
   PRIMARY KEY (vendor_id));

CREATE TABLE CUSTOMER_ACCOUNTS (
ACCOUNT_NUMBER,
CUSTOMER_ID, VENDOR_ID,
IS_ACTIVE) AS 
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;

ALTER TABLE customer_accounts 
ADD CONSTRAINT customer_accounts_pk PRIMARY KEY (ACCOUNT_NUMBER);

ALTER TABLE CUSTOMER_ACCOUNTS ADD CONSTRAINT ca_customer_fk FOREIGN KEY (CUSTOMER_ID) REFERENCES customers(customer_id);

ALTER TABLE CUSTOMER_ACCOUNTS ADD CONSTRAINT ca_vendor_fk FOREIGN KEY (VENDOR_ID) REFERENCES vendors(vendor_id);

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 
);

ALTER TABLE TRANSACTIONS ADD CONSTRAINT transactions_account_number_fk  FOREIGN KEY (ACCOUNT_NUMBER) REFERENCES customer_accounts
(account_number);

insert  into transactions(
account_number, transaction_type, transaction_amount, transaction_date)
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;

CREATE OR REPLACE FUNCTION   get_customer_balance
(  i_customer_id   IN   customers.customer_id%TYPE
)
RETURN  transactions.transaction_amount%TYPE
IS
  v_balance   transactions.transaction_amount%TYPE;
BEGIN
  SELECT SUM (
                 CASE  t.transaction_type
		     WHEN  'C'
		     THEN  -t.transaction_amount
		     ELSE  t.transaction_amount
	 	 END 
             )
  INTO   v_balance
  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;         -- all customers

  RETURN v_balance;
END  get_customer_balance;
/

CREATE OR REPLACE FUNCTION get_account_balance(
  i_account_number IN TRANSACTIONS.ACCOUNT_NUMBER%TYPE
) RETURN TRANSACTIONS.TRANSACTION_AMOUNT%TYPE
IS
  v_balance TRANSACTIONS.TRANSACTION_AMOUNT%TYPE;
BEGIN
  SELECT SUM(
           CASE transaction_type WHEN 'C' THEN -1 ELSE 1 END 
           * transaction_amount
         )
  INTO   v_balance
  FROM   transactions
  WHERE  account_number = i_account_number -- one account
  OR     i_account_number IS NULL;         -- all accounts
  RETURN v_balance;
END;
/

-- account balance works

SELECT 
    CA.ACCOUNT_NUMBER, 
     C.CUSTOMER_ID, 
     C.FIRST_NAME, 
     C.LAST_NAME,
   get_account_balance(ca.account_number) AS balance 
  FROM CUSTOMER_ACCOUNTS CA
INNER JOIN customers c ON ca.customer_id = c.customer_id; 


-- customer balance works

SELECT C.CUSTOMER_ID,
       C.FIRST_NAME,
       C.LAST_NAME, 
       get_customer_balance(C.CUSTOMER_ID) AS balance
FROM   customers c;

with
  function get_account_balance(
  i_account_number IN TRANSACTIONS.ACCOUNT_NUMBER%TYPE
) RETURN TRANSACTIONS.TRANSACTION_AMOUNT%TYPE
IS
  v_balance TRANSACTIONS.TRANSACTION_AMOUNT%TYPE;
BEGIN
  SELECT SUM(
           CASE transaction_type WHEN 'C' THEN -1 ELSE 1 END 
           * transaction_amount
         )
  INTO   v_balance
  FROM   transactions
  WHERE  account_number = i_account_number -- one account
  OR     i_account_number IS NULL;         -- all accounts
  RETURN v_balance;
END;
prep as (
SELECT C.CUSTOMER_ID,
       C.FIRST_NAME,
       C.LAST_NAME, 
       get_customer_balance(C.CUSTOMER_ID) AS balance,
       dense_rank() over (order by get_customer_balance(c.customer_id) desc) as drn
FROM   customers c)
select customer_id, first_name, last_name, balance
from   prep
where  drn = 2
/

CUSTOMER_ID  FIRST_NAME  LAST_NAME    BALANCE
-----------  ----------  ---------  ---------
X060162      Lisa        Saladino   271127.75

C##SCOTT@XE_21.3.0.0.0> with
  2    function get_account_balance(
  3    i_account_number IN TRANSACTIONS.ACCOUNT_NUMBER%TYPE
  4  ) RETURN TRANSACTIONS.TRANSACTION_AMOUNT%TYPE
  5  IS
  6    v_balance TRANSACTIONS.TRANSACTION_AMOUNT%TYPE;
  7  BEGIN
  8    SELECT SUM(
  9  		CASE transaction_type WHEN 'C' THEN -1 ELSE 1 END
 10  		* transaction_amount
 11  	      )
 12    INTO   v_balance
 13    FROM   transactions
 14    WHERE  account_number = i_account_number -- one account
 15    OR     i_account_number IS NULL; 	-- all accounts
 16    RETURN v_balance;
 17  END;
 18  prep as (
 19  SELECT C.CUSTOMER_ID,
 20  	    C.FIRST_NAME,
 21  	    C.LAST_NAME,
 22  	    get_customer_balance(C.CUSTOMER_ID) AS balance,
 23  	    dense_rank() over (order by get_customer_balance(c.customer_id) desc NULLS LAST) as drn
 24  FROM   customers c)
 25  select customer_id, first_name, last_name, balance
 26  from   prep
 27  where  drn = 2
 28  /

CUSTOME FIRST_N LAST_NAME       BALANCE                                         
------- ------- ------------ ----------                                         
P382319 Tom     Micelli       203704.75                                         

1 row selected.

C##SCOTT@XE_21.3.0.0.0> with
  2    function get_account_balance(
  3    i_account_number IN TRANSACTIONS.ACCOUNT_NUMBER%TYPE
  4  ) RETURN TRANSACTIONS.TRANSACTION_AMOUNT%TYPE
  5  IS
  6    v_balance TRANSACTIONS.TRANSACTION_AMOUNT%TYPE;
  7  BEGIN
  8    SELECT SUM(
  9  		CASE transaction_type WHEN 'C' THEN -1 ELSE 1 END
 10  		* transaction_amount
 11  	      )
 12    INTO   v_balance
 13    FROM   transactions
 14    WHERE  account_number = i_account_number -- one account
 15    OR     i_account_number IS NULL; 	-- all accounts
 16    RETURN v_balance;
 17  END;
 18  prep as (
 19  SELECT C.CUSTOMER_ID,
 20  	    C.FIRST_NAME,
 21  	    C.LAST_NAME,
 22  	    get_customer_balance(C.CUSTOMER_ID) AS balance,
 23  	    dense_rank() over (order by NVL (get_customer_balance(c.customer_id), 0) desc) as drn
 24  FROM   customers c)
 25  select customer_id, first_name, last_name, balance
 26  from   prep
 27  where  drn = 2
 28  /

CUSTOME FIRST_N LAST_NAME       BALANCE                                         
------- ------- ------------ ----------                                         
P382319 Tom     Micelli       203704.75                                         

1 row selected.