Time interval between last occurrence and second last occurrence

Viewed 22

I have a table in Presto sql that contains order_date, order_id and customer_id. I want to identify whether an order is from a new customer based on:

  • if the interval between the customer's last order and their previous order is more than 720 days or if it is first occurrence of the customer_id, then designate that customer/order as a NewCustomerOrder. Otherwise, identify as existing order. I couldn't use interval between min_order date and latest order date because customer may have more than 2 orders. Below is previous version
   all_orders as (
select
        customer_id,
        order_id,
        order_date,
        'customer_feed' as source
from
        datalake.dwh_main.customer_feed
where
        retailer = 'customer'
    and market = 'market'
union
select
        customer_id,
        order_id,
        datestamp as order_date,
        'order_feed' as source
from
        datalake.dwh_main.order_feed
where
        retailer = 'retailer'
    and market = 'market'
    ),
    first_order_dates as (
select
        customer_id,
        min(order_date) as first_order_date
from
        all_orders
group by
        1
    ),
    identify_first_order as (
select
    distinct
            customer_id,
            order_id,
            order_date as datestamp,
            first_order_date,
            order_date = first_order_date as is_first_order
from
            all_orders
left join first_order_dates
        using (customer_id)
0 Answers
Related