Новые против старых клиентов sql

На основании комментариев я понимаю, что вы хотите разделить клиентов на две группы: клиенты, у которых были первые транзакции в течение периода, должны быть отделены от тех, у которых были транзакции ранее. Для каждой группы вы хотите подсчитать количество клиентов и суммировать стоимость транзакций.

Обратите внимание: ваш sql-код не показывает горячие вычисления qty и count_of_orders, поэтому я оставил это отдельно (но это, вероятно, будет следовать той же логике).

Учитывая данные этого примера:

INDIVIDUAL_ID | DOLLAR_VALUE_US | TXN_DATE  | RAND_ORG_CODE | IS_MERCH | CURRENCY_CODE | LINE_ITEM_AMT_TYPE_CD
------------: | --------------: | :-------- | :------------ | -------: | :------------ | :--------------------
            1 |              10 | 01-FEB-19 | BRAND         |        1 | USD           | S                    
            1 |              10 | 10-FEB-19 | BRAND         |        1 | USD           | S                    
            1 |              10 | 15-FEB-19 | BRAND         |        1 | USD           | S                    
            1 |              10 | 28-FEB-19 | BRAND         |        1 | USD           | S                    
            2 |              11 | 11-FEB-19 | BRAND         |        1 | USD           | S                    
            2 |              11 | 12-FEB-19 | BRAND         |        1 | USD           | S                    
            3 |              11 | 12-FEB-19 | BRAND         |        1 | USD           | S                    

Учитывая диапазон недели с 10 по 16 февраля, клиент 1 является возвращающимся клиентом с двумя транзакциями в окне, а также клиентами 2 и [117 ] являются новыми клиентами соответственно с 2 и 1 транзакциями. Можно ожидать следующий вывод:

TYPE_OF_CUSTOMER    | COUNT_OF_CUSTOMERS | SUM_DOLLAR_VALUE_US
:------------------ | -----------------: | ------------------:
New Customers       |                  2 |                  33
Returning Customers |                  1 |                  20

Чтобы решить эту проблему, вам нужно настроить несколько уровней агрегации. Во-первых, используйте оконную функцию MIN() OVER(), чтобы восстановить дату первой транзакции каждого клиента. Затем отфильтруйте период анализа, разделите клиентов на новые / возвращающиеся группы и соберите потраченные деньги. Наконец, соберите все результаты вместе.

Запрос:

SELECT
    DECODE(is_new, 1, 'New Customers', 'Returning Customers') type_of_customer,
    COUNT(individual_id) count_of_customers,
    SUM(dollar_value_us) sum_dollar_value_us
FROM (
    SELECT
        individual_id,
        SUM(dollar_value_us) dollar_value_us,
        CASE WHEN MIN(txn_date) = min_txn_date THEN 1 ELSE 0 END is_new
    FROM (
        SELECT 
            individual_id, 
            dollar_value_us,
            txn_date,
            MIN(txn_date) OVER(PARTITION BY individual_id) min_txn_date   
        FROM transaction_detail_mv   
        WHERE 
            rand_org_code = 'BRAND'
            AND is_merch = 1
            AND currency_code = 'USD'
            AND line_item_amt_type_cd = 'S'
    ) t
    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

Эта демонстрация на DB Fiddle демонстрирует каждый шаг вычисления.