Hard query with aggregation

Question

I'm using Oracle SQL, and i need help with a hard query.

I have two tables (table_A):

GroupID (int)
ClientID (int)
Age (int)

(table_B):

GroupID (int)
Budget (int)

My query should do for each group of clients:

• Calculate the average ages of the group
• Calculate the average budget of each clients with age like the average age (The age can be bigger or smaller in 1). For example: Average_Age - 1 < Average_Age < Average_Age + 1.
• Output value: (Budget) / (Average Budget)

For example: table_A:

GroupID | ClientID  | Age
    A   |   11      | 26
    A   |   22      | 27
    A   |   33      | 21
    B   |   44      | 22
    B   |   55      | 29
    B   |   66      | 25
    C   |   77      | 23
    C   |   88      | 22
    C   |   99      | 20
    D   |   111     | 24
    D   |   222     | 26
    D   |   333     | 25

table_B:

GroupID | Budget
   A    |  100
   B    |  200
   C    |  300
   D    |  400

None of the values in the tables are fixed. Example of calculation of the Output Value of Group A:

• Average ages in group A: (27+27+21) / 3 = 24.6
• The ClientIDs with ages of 23-25 are: 66, 77, 111, 333. Their Average budget is: (200+300+400*2)/4 = 325
• The output value should be: 100 / 325 = 0.307

The output table should be

 GroupID    |  Output Value
    A       |     0.307
    B       |     ....
    C       |     ....
    D       |     ....

Any recommendations how to do that? I've tried so many ways. I'm feel lost.

Answer 1

Prepare test data (fixed typo in name and type):

drop table table_a;

create table table_a(
GroupID  varchar2(10),
ClientID int,
Age      int
);

drop table table_b;

create table table_b(
GroupID varchar2(10),
Budget  int
);


insert into table_a values('A',   11  , 26);
insert into table_a values('A',   22  , 27);
insert into table_a values('A',   33  , 21);
insert into table_a values('B',   44  , 22);
insert into table_a values('B',   55  , 29);
insert into table_a values('B',   66  , 25);
insert into table_a values('C',   77  , 23);
insert into table_a values('C',   88  , 22);
insert into table_a values('C',   99  , 20);
insert into table_a values('D',   111 , 24);
insert into table_a values('D',   222 , 26);
insert into table_a values('D',   333 , 25);

insert into table_b values('A',   100);
insert into table_b values('B',   200);
insert into table_b values('C',   300);
insert into table_b values('D',   400);

commit;

The query itself:

select a1.GroupId, a2.groupid, a2.clientid, b1.budget/avg(b2.budget) over(partition by a1.groupid) as avg_budget
 from (select GroupId, trunc(avg(age)) as avg_age
        from table_a
       group by GroupId
     ) a1
 inner join table_a a2
    on a2.age between a1.avg_age - 1 and a1.avg_age + 1
 inner join table_b b1
    on b1.groupid = a1.groupid    
 inner join table_b b2
    on b2.groupid = a2.groupid    
order by a1.GroupId, a2.clientid
;            

Result:

GROUPID GROUPID_1   CLIENTID    AVG_BUDGET
A   B   66  0.307692307692308
A   C   77  0.307692307692308
A   D   111 0.307692307692308
A   D   333 0.307692307692308
B   A   11  0.666666666666667
B   B   66  0.666666666666667
B   D   111 0.666666666666667
B   D   222 0.666666666666667
B   D   333 0.666666666666667
C   A   33  1.33333333333333
C   B   44  1.33333333333333
C   C   88  1.33333333333333
C   C   99  1.33333333333333
D   A   11  1.33333333333333
D   B   66  1.33333333333333
D   D   111 1.33333333333333
D   D   222 1.33333333333333
D   D   333 1.33333333333333