Блог

Задача: получить общее количество уникальных кумулятивных клиентов по каждой причине снижения и по каждому дню.

     Input data sample:
     --------- -------------- ------------ ------ 
    | Cust_Id |  Decline_Dt  |  Reason    | Days |
     --------- -------------- ------------ ------ 
    |   A     |  08-09-2020  |  Reason_1  |   0  |
    |   A     |  08-09-2020  |  Reason_1  |   1  | 
    |   A     |  08-09-2020  |  Reason_1  |   2  |
    |   A     |  08-09-2020  |  Reason_1  |   4  |
    |   B     |  08-09-2020  |  Reason_1  |   0  |
    |   B     |  08-09-2020  |  Reason_1  |   2  |
    |   B     |  08-09-2020  |  Reason_1  |   3  |
    |   C     |  08-09-2020  |  Reason_1  |   1  |
     --------- -------------- ------------ ------    
1) Decline_dt - The date on which the payment was declined. (Ignore it for this task)
2) Days - Indicates the # of days after the payment decline happened, the customer interacted with IVR channel. 
3) Reason - Indicates the payment decline reason 
    
    --Expected Output:
     --------------- ----------- --------------- ---------------------------- 
    |   Reason      |   Days    | Unique_mtns   | total_cumulative_customers |
     --------------- ----------- --------------- ---------------------------- 
    |   Reason_1    |   0       |   2           |           2                |              
    |   Reason_1    |   1       |   2           |           3                |
    |   Reason_1    |   2       |   2           |           3                | 
    |   Reason_1    |   3       |   1           |           3                | 
    |   Reason_1    |   4       |   1           |           3                | 
     ------------------------------------------------------------------------ 
 

Мой запрос улья:

 select a.Reason
        , a.days
        -- , count(distinct a.cust_id) as unique_mtns
        , count(distinct a.cust_id) over (partition by Reason 
                                                order by a.days rows between unbounded preceding and current row) 
                                        as total_cumulative_customers
from table as a  
group by a.reason
        , a.days
 

Вывод (неверный):

  --------------- ----------- ---------------------------- 
|   Reason      |   Days    | total_cumulative_customers |
 --------------- ----------- ---------------------------- 
|   Reason_1    |   0       |               2            |              
|   Reason_1    |   1       |               2            |
|   Reason_1    |   2       |               2            | 
|   Reason_1    |   3       |               1            | 
|   Reason_1    |   4       |               1            | 
 -------------------------------------------------------- 
 

В идеале я бы ожидал, что оконная функция будет выполняться без группирования.
Однако я получаю сообщение об ошибке без группирования. Когда я использую group by, я не получаю кумулятивных клиентов.

Если я правильно вас понял, вы можете использовать подзапрос для вычисления первого дня для каждого кортежа клиентов / причин, а затем выполнить условную агрегацию:

 select reason, days, 
    count(distinct cust_id) as unique_mtns,
    sum(sum(case when days = min_days then 1 else 0 end)) 
        over(partition by reason order by days) as total_cumulative_customers
from (
    select reason, cust_id, 
        min(days) over(partition by reason, cust_id) as min_days
    from mytable
) t
group by reason, days
 

Я бы рекомендовал использовать row_number() для перечисления строк или данного клиента и причины. Ваш код использует count(distinct) идентификатор пользователя, предполагая, что у вас могут быть дубликаты в данный день.

Это было бы:

 select reason, days, count(distinct cust_id) as unique_mtns,
       sum(sum(case when seqnum = 1 then 1 else 0 end)) over (partition by reason order by days) as total_cumulative_customers
from (select t.*,
             row_number() over (partition by reason, cust_id order by days) as seqnum
      from t
     ) t
group by reason, days
order by reason, days;