Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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 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 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 
		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'


INDIVIDUAL_ID
DOLLAR_VALUE_US
TXN_DATE
QUANTITY
TRANSACTION_NUMBER
MIN_TXN_DATE


1
10
01-FEB-19
30
12345
01-FEB-19

1
10
10-FEB-19
40
345
01-FEB-19

1
10
15-FEB-19
50
678
01-FEB-19

1
10
28-FEB-19
70
975
01-FEB-19

2
11
11-FEB-19
80
910
11-FEB-19

2
11
12-FEB-19
90
323
11-FEB-19

3
11
12-FEB-19
62
855
12-FEB-19


SELECT
    individual_id,
    SUM(dollar_value_us),
    sum(quantity),
    count(distinct transaction_number),
    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
    


INDIVIDUAL_ID
SUM(DOLLAR_VALUE_US)
SUM(QUANTITY)
COUNT(DISTINCTTRANSACTION_NUMBER)
IS_NEW


3
11
62
1
1

2
22
170
2
1

1
20
90
2
0


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
    )
x GROUP BY is_new


TYPE_OF_CUSTOMER
COUNT_OF_CUSTOMERS
COUNT_OF_TRANSACTIONS
SUM_OF_QUANTITY


New Customers
2
3
232

Returning Customers
1
2
90
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