clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2335878 fiddles created (27451 in the last week).

create table gender_details (individual_id int, gender varchar(5),age int);
 hidden batch(es)


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) );
 hidden batch(es)


insert into gender_details values(3 ,'M',21);
1 rows affected
 hidden batch(es)


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


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


insert into gender_details values(4 ,'M', 41);
1 rows affected
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


select * from gender_details;
INDIVIDUAL_ID GENDER AGE
3 M 21
2 F 51
1 M 41
4 M 41
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)