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),
    trantype varchar(25)
);
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 gender_details values(4 ,'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','POS');
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','POS'); 
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','ONLINE');
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','ONLINE');
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','POS');
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','ONLINE');
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','POS');
1 rows affected

insert into transaction_detail_mv values(4, 11, 30,866, TO_DATE('12-05-2018', 'DD-MM-YYYY'), 'BRAND', 1, 'USD', 'S','ONLINE');
1 rows affected

insert into transaction_detail_mv values(4, 11, 30,869, TO_DATE('12-02-2019', 'DD-MM-YYYY'), 'BRAND', 1, 'USD', 'S','POS');
1 rows affected

select * from gender_details;


INDIVIDUAL_ID
GENDER
AGE


3
M
21

2
F
51

1
M
41

4
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
TRANTYPE


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

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

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

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

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

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

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

4
11
30
866
12-MAY-18
BRAND
1
USD
S
ONLINE

4
11
30
869
12-FEB-19
BRAND
1
USD
S
POS


SELECT 
		a.individual_id, 
		a.dollar_value_us,
		a.txn_date,
		a.quantity,
		a.transaction_number,
		b.gender,
		b.age,
		MIN(a.txn_date) OVER(PARTITION BY a.individual_id) min_txn_date,
		A.TRANTYPE
	FROM transaction_detail_mv   a
	join gender_details b on a.individual_id = b.individual_id
	WHERE 
		a.brand_org_code = 'BRAND'
		AND a.is_merch = 1
		AND a.currency_code = 'USD'
		AND a.line_item_amt_type_cd = 'S'
		


INDIVIDUAL_ID
DOLLAR_VALUE_US
TXN_DATE
QUANTITY
TRANSACTION_NUMBER
GENDER
AGE
MIN_TXN_DATE
TRANTYPE


1
10
01-FEB-19
30
12345
M
41
01-FEB-19
POS

1
10
10-FEB-19
40
345
M
41
01-FEB-19
POS

1
10
15-FEB-19
50
678
M
41
01-FEB-19
ONLINE

1
10
28-FEB-19
70
975
M
41
01-FEB-19
ONLINE

2
11
11-FEB-19
80
910
F
51
11-FEB-19
POS

2
11
12-FEB-19
90
323
F
51
11-FEB-19
ONLINE

3
11
12-FEB-19
62
855
M
21
12-FEB-19
POS

4
11
12-MAY-18
30
866
M
41
12-MAY-18
ONLINE

4
11
12-FEB-19
30
869
M
41
12-MAY-18
POS


SELECT 
		a.individual_id, 
		a.dollar_value_us,
		a.txn_date,
		a.quantity,
		a.transaction_number,
		b.gender,
		b.age,
		MIN(a.txn_date) OVER(PARTITION BY a.individual_id) min_txn_date,
		A.TRANTYPE
		         
	FROM transaction_detail_mv   a
	join gender_details b on a.individual_id = b.individual_id
	WHERE 
		a.brand_org_code = 'BRAND'
		AND a.is_merch = 1
		AND a.currency_code = 'USD'
		AND a.line_item_amt_type_cd = 'S'
		AND a.transaction_number not in (select transaction_number from transaction_detail_mv where trantype = 'ONLINE' )

		


INDIVIDUAL_ID
DOLLAR_VALUE_US
TXN_DATE
QUANTITY
TRANSACTION_NUMBER
GENDER
AGE
MIN_TXN_DATE
TRANTYPE


1
10
10-FEB-19
40
345
M
41
01-FEB-19
POS

1
10
01-FEB-19
30
12345
M
41
01-FEB-19
POS

2
11
11-FEB-19
80
910
F
51
11-FEB-19
POS

3
11
12-FEB-19
62
855
M
21
12-FEB-19
POS

4
11
12-FEB-19
30
869
M
41
12-FEB-19
POS


SELECT 
		a.individual_id, 
		a.dollar_value_us,
		a.txn_date,
		a.quantity,
		a.transaction_number,
		b.gender,
		b.age,
		MIN(a.txn_date) OVER(PARTITION BY a.individual_id) min_txn_date,
		A.TRANTYPE
		         
	FROM transaction_detail_mv   a
	join gender_details b on a.individual_id = b.individual_id
	WHERE 
		a.brand_org_code = 'BRAND'
		AND a.is_merch = 1
		AND a.currency_code = 'USD'
		AND a.line_item_amt_type_cd = 'S'
		AND a.transaction_number in (select transaction_number from transaction_detail_mv where trantype = 'ONLINE' )

		


INDIVIDUAL_ID
DOLLAR_VALUE_US
TXN_DATE
QUANTITY
TRANSACTION_NUMBER
GENDER
AGE
MIN_TXN_DATE
TRANTYPE


1
10
28-FEB-19
70
975
M
41
15-FEB-19
ONLINE

1
10
15-FEB-19
50
678
M
41
15-FEB-19
ONLINE

2
11
12-FEB-19
90
323
F
51
12-FEB-19
ONLINE

4
11
12-MAY-18
30
866
M
41
12-MAY-18
ONLINE


SELECT
    DECODE(is_new, 1, 'New Customers', 'Returning Customers') type_of_customer,
    COUNT(distinct individual_id) count_of_customers,
    SUM(count_of_transactions) count_of_transactions,
    SUM(sum_of_quantity) sum_of_quantity
FROM (
    SELECT
    individual_id,
    SUM(dollar_value_us),
    sum(quantity) sum_of_quantity,
    count(distinct transaction_number) count_of_transactions,
    CASE WHEN MIN(txn_date) = min_txn_date THEN 1 ELSE 0 END is_new
FROM (
    SELECT 
		a.individual_id, 
		a.dollar_value_us,
		a.txn_date,
		a.quantity,
		a.transaction_number,
		b.gender,
		b.age,
		MIN(a.txn_date) OVER(PARTITION BY a.individual_id) min_txn_date,
		A.TRANTYPE
		         
	FROM transaction_detail_mv   a
	join gender_details b on a.individual_id = b.individual_id
	WHERE 
		a.brand_org_code = 'BRAND'
		AND a.is_merch = 1
		AND a.currency_code = 'USD'
		AND a.line_item_amt_type_cd = 'S'
		AND a.transaction_number not in (select transaction_number from transaction_detail_mv where trantype = 'ONLINE' )

)
WHERE 
	txn_date >= TO_DATE('10-02-2019', 'DD-MM-YYYY') 
	AND txn_date < TO_DATE('17-02-2019', 'DD-MM-YYYY')
GROUP BY
    individual_id,
    min_txn_date
    )
GROUP BY is_new


TYPE_OF_CUSTOMER
COUNT_OF_CUSTOMERS
COUNT_OF_TRANSACTIONS
SUM_OF_QUANTITY


New Customers
3
3
172

Returning Customers
1
1
40


SELECT
    DECODE(is_new, 1, 'New Customers', 'Returning Customers') type_of_customer,
    COUNT(distinct individual_id) count_of_customers,
    SUM(count_of_transactions) count_of_transactions,
    SUM(sum_of_quantity) sum_of_quantity
FROM (
    SELECT
    individual_id,
    SUM(dollar_value_us),
    sum(quantity) sum_of_quantity,
    count(distinct transaction_number) count_of_transactions,
    CASE WHEN MIN(txn_date) = min_txn_date THEN 1 ELSE 0 END is_new
FROM (
    SELECT 
		a.individual_id, 
		a.dollar_value_us,
		a.txn_date,
		a.quantity,
		a.transaction_number,
		b.gender,
		b.age,
		MIN(a.txn_date) OVER(PARTITION BY a.individual_id) min_txn_date,
		A.TRANTYPE
		         
	FROM transaction_detail_mv   a
	join gender_details b on a.individual_id = b.individual_id
	WHERE 
		a.brand_org_code = 'BRAND'
		AND a.is_merch = 1
		AND a.currency_code = 'USD'
		AND a.line_item_amt_type_cd = 'S'
		AND a.transaction_number in (select transaction_number from transaction_detail_mv where trantype = 'ONLINE' )

		
)
WHERE 
	txn_date >= TO_DATE('10-02-2019', 'DD-MM-YYYY') 
	AND txn_date < TO_DATE('17-02-2019', 'DD-MM-YYYY')
GROUP BY
    individual_id,
    min_txn_date
    )
GROUP BY is_new


TYPE_OF_CUSTOMER
COUNT_OF_CUSTOMERS
COUNT_OF_TRANSACTIONS
SUM_OF_QUANTITY


New Customers
2
2
140


SELECT
    DECODE(is_new, 1, 'New Customers', 'Returning Customers') type_of_customer,
    COUNT(distinct individual_id) count_of_customers,
    SUM(count_of_transactions) count_of_transactions,
    SUM(sum_of_quantity) sum_of_quantity
FROM (
    SELECT
    individual_id,
    SUM(dollar_value_us),
    sum(quantity) sum_of_quantity,
    count(distinct transaction_number) count_of_transactions,
    CASE WHEN MIN(txn_date) = min_txn_date THEN 1 ELSE 0 END is_new
FROM (
    SELECT 
		a.individual_id, 
		a.dollar_value_us,
		a.txn_date,
		a.quantity,
		a.transaction_number,
		b.gender,
		b.age,
		MIN(a.txn_date) OVER(PARTITION BY a.individual_id) min_txn_date,
		A.TRANTYPE
		         
	FROM transaction_detail_mv   a
	join gender_details b on a.individual_id = b.individual_id
	WHERE 
		a.brand_org_code = 'BRAND'
		AND a.is_merch = 1
		AND a.currency_code = 'USD'
		AND a.line_item_amt_type_cd = 'S'
		

		
)
WHERE 
	txn_date >= TO_DATE('10-02-2019', 'DD-MM-YYYY') 
	AND txn_date < TO_DATE('17-02-2019', 'DD-MM-YYYY')
GROUP BY
    individual_id,
    min_txn_date
    )
GROUP BY is_new


TYPE_OF_CUSTOMER
COUNT_OF_CUSTOMERS
COUNT_OF_TRANSACTIONS
SUM_OF_QUANTITY


New Customers
2
3
232

Returning Customers
2
3
120



select gender, 
case when age < 18 then '<18'
when age between 18 and 24 then '18-24'
when age between 25 and 32 then '25-32'
when age between 33 and 39 then '35-39'
when age between 40 and 46 then '40-46'
when age between 47 and 53 then '46-52'
when age between 54 and 60 then '53-58'
when age > 60 then '61+' end as AgeGroup
, count(distinct individual_id) indiv
, count (distinct transaction_number) txn_count
, sum(dollar_value_us) as Spend
, sum(quantity) Qty

from (SELECT 
		a.individual_id, 
		a.dollar_value_us,
		a.txn_date,
		a.quantity,
		a.transaction_number,
		b.gender,
		b.age,
		MIN(txn_date) OVER(PARTITION BY a.individual_id) min_txn_date          
	FROM transaction_detail_mv   a
	join gender_details b on a.individual_id = b.individual_id
	WHERE 
		a.brand_org_code = 'BRAND'
		AND a.is_merch = 1
		AND a.currency_code = 'USD'
		AND a.line_item_amt_type_cd = 'S')
		
where trunc(txn_date) >= TO_DATE('10-02-2019', 'DD-MM-YYYY') 
	AND trunc(txn_date) < TO_DATE('17-02-2019', 'DD-MM-YYYY')
	AND min_txn_date >= TO_DATE('10-02-2019', 'DD-MM-YYYY')
	AND min_txn_date < TO_DATE('17-02-2019', 'DD-MM-YYYY')


group by gender, 
case when age < 18 then '<18'
when age between 18 and 24 then '18-24'
when age between 25 and 32 then '25-32'
when age between 33 and 39 then '35-39'
when age between 40 and 46 then '40-46'
when age between 47 and 53 then '46-52'
when age between 54 and 60 then '53-58'
when age > 60 then '61+' end


GENDER
AGEGROUP
INDIV
TXN_COUNT
SPEND
QTY


F
46-52
1
2
22
170

M
18-24
1
1
11
62



select gender, 
case when age < 18 then '<18'
when age between 18 and 24 then '18-24'
when age between 25 and 32 then '25-32'
when age between 33 and 39 then '35-39'
when age between 40 and 46 then '40-46'
when age between 47 and 53 then '46-52'
when age between 54 and 60 then '53-58'
when age > 60 then '61+' end as AgeGroup
, count(distinct individual_id) indiv
, count (distinct transaction_number) txn_count
, sum(dollar_value_us) as Spend
, sum(quantity) Qty

from (SELECT 
		a.individual_id, 
		a.dollar_value_us,
		a.txn_date,
		a.quantity,
		a.transaction_number,
		b.gender,
		b.age,
		MIN(txn_date) OVER(PARTITION BY a.individual_id) min_txn_date          
	FROM transaction_detail_mv   a
	join gender_details b on a.individual_id = b.individual_id
	WHERE 
		a.brand_org_code = 'BRAND'
		AND a.is_merch = 1
		AND a.currency_code = 'USD'
		AND a.line_item_amt_type_cd = 'S')
		
where txn_date >= TO_DATE('10-02-2019', 'DD-MM-YYYY') 
	AND txn_date < TO_DATE('17-02-2019', 'DD-MM-YYYY')
	AND min_txn_date <TO_DATE('10-02-2019', 'DD-MM-YYYY')


group by gender, 
case when age < 18 then '<18'
when age between 18 and 24 then '18-24'
when age between 25 and 32 then '25-32'
when age between 33 and 39 then '35-39'
when age between 40 and 46 then '40-46'
when age between 47 and 53 then '46-52'
when age between 54 and 60 then '53-58'
when age > 60 then '61+' end


GENDER
AGEGROUP
INDIV
TXN_COUNT
SPEND
QTY


M
40-46
2
3
31
120


SELECT
    DECODE(is_new, 1, 'New Customers', 'Returning Customers') type_of_customer,
    COUNT(distinct individual_id) count_of_customers,
    SUM(count_of_transactions) count_of_transactions,
    SUM(sum_of_quantity) sum_of_quantity
FROM (
    SELECT
    individual_id,
    SUM(dollar_value_us),
    sum(quantity) sum_of_quantity,
    count(distinct transaction_number) count_of_transactions,
    CASE WHEN MIN(txn_date) = min_txn_date THEN 1 ELSE 0 END is_new
FROM (
    SELECT 
		individual_id, 
		dollar_value_us,
		txn_date,
		quantity,
		transaction_number,
		MIN(txn_date) OVER(PARTITION BY individual_id) min_txn_date          
	FROM transaction_detail_mv   
	WHERE 
		brand_org_code = 'BRAND'
		AND is_merch = 1
		AND currency_code = 'USD'
		AND line_item_amt_type_cd = 'S'
)
WHERE 
	txn_date >= TO_DATE('10-02-2019', 'DD-MM-YYYY') 
	AND txn_date < TO_DATE('17-02-2019', 'DD-MM-YYYY')
GROUP BY
    individual_id,
    min_txn_date
    )
GROUP BY is_new


TYPE_OF_CUSTOMER
COUNT_OF_CUSTOMERS
COUNT_OF_TRANSACTIONS
SUM_OF_QUANTITY


New Customers
2
3
232

Returning Customers
2
3
120


SELECT
    DECODE(is_new, 1, 'New Customers', 'Returning Customers') type_of_customer,
    COUNT(distinct individual_id) count_of_customers,
    SUM(count_of_transactions) count_of_transactions,
    SUM(sum_of_quantity) sum_of_quantity
FROM (
    SELECT
    individual_id,
    SUM(dollar_value_us),
    sum(quantity) sum_of_quantity,
    count(distinct transaction_number) count_of_transactions,
    CASE WHEN MIN(txn_date) = min_txn_date THEN 1 ELSE 0 END is_new
FROM (
    SELECT 
		individual_id, 
		dollar_value_us,
		txn_date,
		quantity,
		transaction_number,
		MIN(txn_date) OVER(PARTITION BY individual_id) min_txn_date          
	FROM transaction_detail_mv   
	WHERE 
		brand_org_code = 'BRAND'
		AND is_merch = 1
		AND currency_code = 'USD'
		AND line_item_amt_type_cd = 'S'
		AND INDIVIDUAL_ID in (select INDIVIDUAL_ID from transaction_detail_mv where TRANTYPE = 'ONLINE' GROUP BY individual_id HAVING COUNT(DISTINCT trantype) = 1)
)
WHERE 
	txn_date >= TO_DATE('10-02-2019', 'DD-MM-YYYY') 
	AND txn_date < TO_DATE('17-02-2019', 'DD-MM-YYYY')
GROUP BY
    individual_id,
    min_txn_date
    )
GROUP BY is_new


TYPE_OF_CUSTOMER
COUNT_OF_CUSTOMERS
COUNT_OF_TRANSACTIONS
SUM_OF_QUANTITY


New Customers
1
2
170

Returning Customers
2
3
120


SELECT
    DECODE(is_new, 1, 'New Customers', 'Returning Customers') type_of_customer,
    COUNT(distinct individual_id) count_of_customers,
    SUM(count_of_transactions) count_of_transactions,
    SUM(sum_of_quantity) sum_of_quantity
FROM (
    SELECT
    individual_id,
    SUM(dollar_value_us),
    sum(quantity) sum_of_quantity,
    count(distinct transaction_number) count_of_transactions,
    CASE WHEN MIN(txn_date) = min_txn_date THEN 1 ELSE 0 END is_new
FROM (
    SELECT 
		individual_id, 
		dollar_value_us,
		txn_date,
		quantity,
		transaction_number,
		MIN(txn_date) OVER(PARTITION BY individual_id) min_txn_date          
	FROM transaction_detail_mv   
	WHERE 
		brand_org_code = 'BRAND'
		AND is_merch = 1
		AND currency_code = 'USD'
		AND line_item_amt_type_cd = 'S'
		AND INDIVIDUAL_ID in (select DISTINCT INDIVIDUAL_ID from transaction_detail_mv where TRANTYPE = 'POS' GROUP BY individual_id HAVING COUNT(DISTINCT trantype) = 1)
)
WHERE 
	txn_date >= TO_DATE('10-02-2019', 'DD-MM-YYYY') 
	AND txn_date < TO_DATE('17-02-2019', 'DD-MM-YYYY')
GROUP BY
    individual_id,
    min_txn_date
    )
GROUP BY is_new


TYPE_OF_CUSTOMER
COUNT_OF_CUSTOMERS
COUNT_OF_TRANSACTIONS
SUM_OF_QUANTITY


New Customers
2
3
232

Returning Customers
2
3
120
INDIVIDUAL_ID	DOLLAR_VALUE_US	QUANTITY	TRANSACTION_NUMBER	TXN_DATE	BRAND_ORG_CODE	IS_MERCH	CURRENCY_CODE	LINE_ITEM_AMT_TYPE_CD	TRANTYPE
1	10	30	12345	01-FEB-19	BRAND	1	USD	S	POS
1	10	40	345	10-FEB-19	BRAND	1	USD	S	POS
1	10	50	678	15-FEB-19	BRAND	1	USD	S	ONLINE
1	10	70	975	28-FEB-19	BRAND	1	USD	S	ONLINE
2	11	80	910	11-FEB-19	BRAND	1	USD	S	POS
2	11	90	323	12-FEB-19	BRAND	1	USD	S	ONLINE
3	11	62	855	12-FEB-19	BRAND	1	USD	S	POS
4	11	30	866	12-MAY-18	BRAND	1	USD	S	ONLINE
4	11	30	869	12-FEB-19	BRAND	1	USD	S	POS