SQL & PL/SQL

Hi Experts,

Please let me know if I am posting in wrong category(also let me know the exact path where can I post this question)

We have items in system as below (under the table: XXHH_ABC_CLASSIFICATION)

CREATE TABLE xxhh_abc_classification (
    item_id      NUMBER,
    item_name    VARCHAR2(100),
    org_id       NUMBER,
    org_name     VARCHAR2(30),
    bu_name      VARCHAR2(100),
    item_revenue NUMBER
);

insert into xxhh_abc_classification values(1,'ABC',100,'ONT01','US',100.5);
insert into xxhh_abc_classification values(1,'ABC',101,'CHA01','US',200.5);
insert into xxhh_abc_classification values(1,'ABC',103,'DE3PL','EU',300.5);
insert into xxhh_abc_classification values(2,'XYZ',100,'ONT01','US',401);
insert into xxhh_abc_classification values(2,'XYZ',101,'CHA01','US',501);
insert into xxhh_abc_classification values(2,'XYZ',103,'DE3PL','EU',601);
insert into xxhh_abc_classification values(3,'MNO',100,'ONT01','US',705.93);
insert into xxhh_abc_classification values(3,'MNO',101,'CHA01','US',805.93);
insert into xxhh_abc_classification values(3,'MNO',103,'DE3PL','EU',905.93);
insert into xxhh_abc_classification values(4,'PQR',100,'ONT01','US',1500.16);
insert into xxhh_abc_classification values(4,'PQR',101,'CHA01','US',1600.16);
insert into xxhh_abc_classification values(4,'PQR',103,'DE3PL','EU',1700.16);

Our requirement is:
We need to classify the items based on revenue(Item-BU wise). Items whose sum of revenue % is ≥50% has be under Class A. Items whose revenue is ≥40 but <50 has to be under Class B. All other items should come under Class C.

Example: As shown below, under ‘EU’ Business Unit, items PQR and MNO will come under Class-A as there sum of Revenue% is more than 50%. And Items XYZ and ABC will come under Class-C as there sum of Revenue% is still less than 30%

Similarly, for ‘US’ business unit, Items PQR, MNO will come under Class-A. All other items is Class-B(as all the remaining items sum of revenue % is still less than 50, but more than 40)

We need to develop through a query(only through query the client is expecting, not with any pl/sql block). Can anyone please help me on this.

Table we are using is: XXHH_ABC_CLASSIFICATION.

Thanks in Advance,
Bommi