Forgive me if I word this poorly. And sorry if it has already been asked, but I was not able to find an answer here.

I'm using Snowflake to try and do the below.

Basically, I'm trying to do a piece of work to find out how many times a customer as placed an order after a specific date for each customer.

Scenario: We want to see if customers continue to shop with us after they have been short-shipped (received 1 or more items less than they ordered).

So for example:
customer 1 places an order on 01/01/2020 and this was a short-shipment.
they then go on to place an order 06/06/2020 and 02/02/2021.
so this customer has a total of 2 additional orders since they were short-shipped on 01/01/2020.\

customer 2 places an order on 02/03/2020 and this was short-shipped.
customer 2 has not since placed an order, so they will have 0 additional orders.

Data available:

Desired output:

CodePudding user response：

So using a boosted version of your data:

with data_cte( cust_id, ord_id, order_date, short_order_flg) as (
    select * from values
    (1, '1', '2018-06-06'::date, false),
    (1, '2', '2019-01-01'::date, true), 
    (1, '3', '2019-06-06'::date, false),
    (1, '4', '2019-12-02'::date, false),
    (1, '5', '2020-01-01'::date, true), 
    (1, '6', '2020-06-06'::date, false),
    (1, '7', '2021-02-02'::date, false),
    (2, '8', '2020-01-01'::date, true)
)

which shows a "valid" purchase, multiple "short ships" and how to batch them

SELECT 
    cust_id, 
    min(order_date) as short_date, 
    count(*) -1 as follow_count
FROM (
    select 
        cust_id
        ,order_date
        ,CONDITIONAL_TRUE_EVENT(short_order_flg) over(partition by cust_id order by order_date ) as edge
    from data_cte
)
where edge > 0
group by 1, edge
order by 1,2;

gives:

The key things to note, CONDITIONAL_TRUE_EVENT increases each time the event happen, which gives cust_id,edge value as batch key, and if the event has not happened those lines are zero, thus the WHERE filter.

The last things is given we have atleast one count for the start of "post short" batch, we need to subtract one from the count.

CodePudding user response：

Try this

with CTE as (
select 1 as cust_id, '0123' as ord_id, '2020-01-01'::date as order_date, 1 as short_order_flg union all 
select 1 as cust_id, '0456' as ord_id, '2020-06-06'::date as order_date, 0 as short_order_flg union all 
select 1 as cust_id, '0789' as ord_id, '2021-02-02'::date as order_date, 0 as short_order_flg union all 
select 2 as cust_id, '1011' as ord_id, '2020-01-01'::date as order_date, 1 as short_order_flg
),
following_orders as (
select cust_id, short_order_flg, count(ord_id) over (partition by cust_id order by order_date rows between current row and unbounded following) - 1 as number_of_orders
from cte
order by cust_id, order_date
)
select cust_id, number_of_orders
from following_orders
where short_order_flg = 1
;

I added column short_order_flg to indicate which record represents the short order. Then I used window function count(ord_id) over(...) to calculate the number of orders following each order, subtracting 1 to exclude the current record itself. Finally, I applied a filter to select only the short order records.