Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table gender_details (individual_id int,
gender varchar(5),age int);
create table transaction_detail_mv (
    individual_id int,
    dollar_value_us int,
    quantity int,
    transaction_number int,
    txn_date date,
    brand_org_code varchar(5),
    is_merch int,
    currency_code varchar(5),
    line_item_amt_type_cd varchar(1)
);
insert into gender_details values(3 ,'M',21);
1 rows affected

insert into gender_details values(2 ,'F', 51);
1 rows affected

insert into gender_details values(1 ,'M', 41);
1 rows affected

insert into transaction_detail_mv values(1, 10, 30,12345, TO_DATE('01-02-2019', 'DD-MM-YYYY'), 'BRAND', 1, 'USD', 'S');
1 rows affected

insert into transaction_detail_mv values(1, 10,40,345, TO_DATE('10-02-2019', 'DD-MM-YYYY'), 'BRAND', 1, 'USD', 'S'); 
1 rows affected

insert into transaction_detail_mv values(1, 10, 50,678,TO_DATE('15-02-2019', 'DD-MM-YYYY'), 'BRAND', 1, 'USD', 'S');
1 rows affected

insert into transaction_detail_mv values(1, 10,70,975, TO_DATE('28-02-2019', 'DD-MM-YYYY'), 'BRAND', 1, 'USD', 'S');
1 rows affected

insert into transaction_detail_mv values(2, 11,80,910, TO_DATE('11-02-2019', 'DD-MM-YYYY'), 'BRAND', 1, 'USD', 'S');
1 rows affected

insert into transaction_detail_mv values(2, 11, 90,323, TO_DATE('12-02-2019', 'DD-MM-YYYY'), 'BRAND', 1, 'USD', 'S');
1 rows affected

insert into transaction_detail_mv values(3, 11, 62,855, TO_DATE('12-02-2019', 'DD-MM-YYYY'), 'BRAND', 1, 'USD', 'S');
1 rows affected

select * from gender_details;


INDIVIDUAL_ID
GENDER
AGE


3
M
21

2
F
51

1
M
41


select * from transaction_detail_mv


INDIVIDUAL_ID
DOLLAR_VALUE_US
QUANTITY
TRANSACTION_NUMBER
TXN_DATE
BRAND_ORG_CODE
IS_MERCH
CURRENCY_CODE
LINE_ITEM_AMT_TYPE_CD


1
10
30
12345
01-FEB-19
BRAND
1
USD
S

1
10
40
345
10-FEB-19
BRAND
1
USD
S

1
10
50
678
15-FEB-19
BRAND
1
USD
S

1
10
70
975
28-FEB-19
BRAND
1
USD
S

2
11
80
910
11-FEB-19
BRAND
1
USD
S

2
11
90
323
12-FEB-19
BRAND
1
USD
S

3
11
62
855
12-FEB-19
BRAND
1
USD
S


  select a.gender, 
case when a.age < 18 then '<18'
when a.age between 18 and 24 then '18-24'
when a.age between 25 and 32 then '25-32'
when a.age between 33 and 39 then '35-39'
when a.age between 40 and 46 then '40-46'
when a.age between 47 and 53 then '46-52'
when a.age between 54 and 60 then '53-58'
when a.age > 60 then '61+' end as AgeGroup
, count(distinct b.individual_id)
,count(distinct b.transaction_number) as count
from transaction_detail_mv b
join gender_details a
on b.individual_id = a.individual_id

where b.brand_org_code = 'BRAND'
and txn_date >= TO_DATE('10-02-2019', 'DD-MM-YYYY') 
	AND txn_date < TO_DATE('17-02-2019', 'DD-MM-YYYY')
and b.is_merch = 1
and b.line_item_amt_type_cd = 'S'
group by a.gender, 
case when a.age < 18 then '<18'
when a.age between 18 and 24 then '18-24'
when a.age between 25 and 32 then '25-32'
when a.age between 33 and 39 then '35-39'
when a.age between 40 and 46 then '40-46'
when a.age between 47 and 53 then '46-52'
when a.age between 54 and 60 then '53-58'
when a.age > 60 then '61+' end 
order by 1,2


GENDER
AGEGROUP
COUNT(DISTINCTB.INDIVIDUAL_ID)
COUNT


F
46-52
1
2

M
18-24
1
1

M
40-46
1
2

INDIVIDUAL_ID	DOLLAR_VALUE_US	QUANTITY	TRANSACTION_NUMBER	TXN_DATE	BRAND_ORG_CODE	IS_MERCH	CURRENCY_CODE	LINE_ITEM_AMT_TYPE_CD
1	10	30	12345	01-FEB-19	BRAND	1	USD	S
1	10	40	345	10-FEB-19	BRAND	1	USD	S
1	10	50	678	15-FEB-19	BRAND	1	USD	S
1	10	70	975	28-FEB-19	BRAND	1	USD	S
2	11	80	910	11-FEB-19	BRAND	1	USD	S
2	11	90	323	12-FEB-19	BRAND	1	USD	S
3	11	62	855	12-FEB-19	BRAND	1	USD	S