question

Hello I am trying to find the customers who just shop online and just shop in store and the customers who shop both online and in store. So when I add them up they should be equal to my total customers.

I am trying to find the new and returning customer by their shopping channel. I need a sql to give me all the new customer and returning customers who have shopped in store, and then in a separate table all the new/returning customers who have shopped only online and then people who have shopped both online and in store (crossover customers). So that when I add off them together they should be equal to my total customers in each category (new and returning). It should look like below:

how data should look like

I have created a sample database as well. I am also trying to break the customer by new and returning customers and later by their age range.

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=96a7b85c8ca0da7f7c40f20205964d9b

these are some of the queries which I have tried: Below is the one which shows me the new and the returning customers who have only bough online:

<code>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,CASEWHEN MIN(txn_date)= min_txn_date THEN1ELSE0END 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(PARTITIONBY 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 =1AND a.currency_code ='USD'AND a.line_item_amt_type_cd ='S'AND a.individual_id notin(select individual_id from transaction_detail_mv where trantype ='POS'))WHERE 
    txn_date >= TO_DATE('10-02-2019','DD-MM-YYYY')AND txn_date < TO_DATE('17-02-2019','DD-MM-YYYY')GROUPBY
    individual_id,
    min_txn_date
    )GROUPBY is_new

and to find the new and returnign customers who buy form POS is bewow:

<code>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,CASEWHEN MIN(txn_date)= min_txn_date THEN1ELSE0END 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(PARTITIONBY 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 =1AND a.currency_code ='USD'AND a.line_item_amt_type_cd ='S'AND a.individual_id notin(select individual_id 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')GROUPBY
        individual_id,
        min_txn_date
        )GROUPBY is_new

I am trying to find new and old customers who have shopped both online and in POS. Please HELP !