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)