Как отфильтровать новых клиентов в SQL?

Question

Мой запрос выглядит так:

SELECT 
  DATE_TRUNC ('month', d.order_date)::date AS month,
  COUNT (DISTINCT (cu.cust_id)) new_custs
FROM sql.store_delivery d
JOIN sql.store_carts c ON d.order_id = c.order_id
JOIN  sql.store_customers cu ON d.cust_id = cu.cust_id
JOIN sql.store_products p ON p.product_id = c.product_id
Where cu.category = 'Corporate' 
GROUP BY 1
HAVING COUNT (d.order_date) >= 1
ORDER BY 1

Но им я получаю количество клиентов по месяцам, а мне нужны новые клиенты.
Как это сделать?

Answer 1

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

 WITH first_orders AS (
  SELECT cu.cust_id, MIN(DATE_TRUNC('month', d.order_date)::date) AS first_order_month
  FROM sql.store_delivery d
  JOIN sql.store_carts c ON d.order_id = c.order_id
  JOIN sql.store_customers cu ON d.cust_id = cu.cust_id
  JOIN sql.store_products p ON p.product_id = c.product_id
  WHERE cu.category = 'Corporate'
  GROUP BY cu.cust_id
)
SELECT 
  first_order_month AS month,
  COUNT(DISTINCT cust_id) AS new_custs
FROM first_orders GROUP BY 1 ORDER BY 1

Answer 2

Для каждого cust_id из store_delivery определите дату первой поставки - это "новый" клиент в этом месяце. Или первая поставка прошла в этом месяце - новый клиент. Будет 1 строка для каждого клиента (DISTINCT не нужен).
Присоединять нужно только таблицу "store_customers" - для фильтра по "category". Остальные соединения таблиц не нужны.
Посчитайте количество таких клиентов в каждом месяце.

Попробуйте так

select month, count(*) firstDeliveryCustCount
from(
   SELECT d.cust_id, min(DATE_TRUNC ('month', d.order_date)::date) AS month
   FROM store_delivery d
   JOIN store_customers cu ON d.cust_id = cu.cust_id
   Where cu.category = 'Corporate' 
   group by d.cust_id
)c1
group by month

БЛОГ НА HUSL

Как отфильтровать новых клиентов в SQL?

Ответы (2 шт):