SQL: Найти первую и последнюю покупку каждого клиента

Решение: Первая и последняя покупка клиента

SQL-запрос (базовый вариант)

SELECT
  customer_id,
  COUNT(*) as total_purchases,
  MIN(purchase_date) as first_purchase_date,
  MAX(purchase_date) as last_purchase_date
FROM purchases
GROUP BY customer_id
ORDER BY customer_id;

Это найдёт даты, но без информации о продуктах.

Вариант со страницей (с использованием оконных функций)

WITH purchases_ranked AS (
  -- Ранжируем покупки каждого клиента по дате
  SELECT
    purchase_id,
    customer_id,
    product_name,
    purchase_date,
    amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY purchase_date ASC) as first_rn,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY purchase_date DESC) as last_rn
  FROM purchases
),
first_purchase AS (
  SELECT
    customer_id,
    product_name as first_product,
    purchase_date as first_date
  FROM purchases_ranked
  WHERE first_rn = 1
),
last_purchase AS (
  SELECT
    customer_id,
    product_name as last_product,
    purchase_date as last_date
  FROM purchases_ranked
  WHERE last_rn = 1
)
SELECT
  p.customer_id,
  fp.first_date as first_purchase_date,
  fp.first_product as first_purchase_product,
  lp.last_date as last_purchase_date,
  lp.last_product as last_purchase_product,
  COUNT(DISTINCT p.purchase_id) as total_purchases
FROM purchases p
LEFT JOIN first_purchase fp ON p.customer_id = fp.customer_id
LEFT JOIN last_purchase lp ON p.customer_id = lp.customer_id
GROUP BY p.customer_id, fp.first_date, fp.first_product, lp.last_date, lp.last_product
ORDER BY p.customer_id;

Оптимизированный вариант (более простой)

WITH purchase_dates AS (
  SELECT
    customer_id,
    COUNT(*) as total_purchases,
    MIN(purchase_date) as first_purchase_date,
    MAX(purchase_date) as last_purchase_date
  FROM purchases
  GROUP BY customer_id
)
SELECT
  pd.customer_id,
  pd.first_purchase_date,
  fp.product_name as first_purchase_product,
  pd.last_purchase_date,
  lp.product_name as last_purchase_product,
  pd.total_purchases
FROM purchase_dates pd
LEFT JOIN purchases fp ON pd.customer_id = fp.customer_id 
  AND pd.first_purchase_date = fp.purchase_date
LEFT JOIN purchases lp ON pd.customer_id = lp.customer_id 
  AND pd.last_purchase_date = lp.purchase_date
-- Берём только первую строку каждого типа (в случае нескольких покупок в один день)
QUALIFY ROW_NUMBER() OVER (PARTITION BY pd.customer_id, pd.first_purchase_date ORDER BY fp.purchase_id) = 1
  OR fp.purchase_id IS NULL
AND ROW_NUMBER() OVER (PARTITION BY pd.customer_id, pd.last_purchase_date ORDER BY lp.purchase_id DESC) = 1
  OR lp.purchase_id IS NULL
ORDER BY pd.customer_id;

Самый простой вариант (с DISTINCT ON)

-- Для PostgreSQL
SELECT DISTINCT ON (customer_id)
  customer_id,
  purchase_date as first_purchase_date,
  product_name as first_purchase_product
FROM purchases
ORDER BY customer_id, purchase_date ASC;

-- Для последней покупки
SELECT DISTINCT ON (customer_id)
  customer_id,
  purchase_date as last_purchase_date,
  product_name as last_purchase_product
FROM purchases
ORDER BY customer_id, purchase_date DESC;

Полное решение (работает везде)

WITH first_last_dates AS (
  SELECT
    customer_id,
    COUNT(*) as total_purchases,
    MIN(purchase_date) as first_purchase_date,
    MAX(purchase_date) as last_purchase_date
  FROM purchases
  GROUP BY customer_id
),
first_purchases AS (
  SELECT
    p.customer_id,
    p.product_name as first_purchase_product,
    p.purchase_date as first_purchase_date,
    ROW_NUMBER() OVER (PARTITION BY p.customer_id ORDER BY p.purchase_id) as rn
  FROM purchases p
  JOIN first_last_dates fld ON p.customer_id = fld.customer_id 
    AND p.purchase_date = fld.first_purchase_date
),
last_purchases AS (
  SELECT
    p.customer_id,
    p.product_name as last_purchase_product,
    p.purchase_date as last_purchase_date,
    ROW_NUMBER() OVER (PARTITION BY p.customer_id ORDER BY p.purchase_id DESC) as rn
  FROM purchases p
  JOIN first_last_dates fld ON p.customer_id = fld.customer_id 
    AND p.purchase_date = fld.last_purchase_date
)
SELECT
  fld.customer_id,
  fp.first_purchase_date,
  fp.first_purchase_product,
  lp.last_purchase_date,
  lp.last_purchase_product,
  fld.total_purchases
FROM first_last_dates fld
LEFT JOIN first_purchases fp ON fld.customer_id = fp.customer_id AND fp.rn = 1
LEFT JOIN last_purchases lp ON fld.customer_id = lp.customer_id AND lp.rn = 1
ORDER BY fld.customer_id;

Пример вывода

customer_id | first_purchase_date | first_purchase_product | last_purchase_date | last_purchase_product | total_purchases
------------|---------------------|------------------------|--------------------|-----------------------|---
1           | 2024-01-15 10:30    | Laptop                 | 2024-03-20 15:45   | Mouse                 | 5
2           | 2024-02-01 08:00    | Phone                  | 2024-02-01 08:00   | Phone                 | 1
3           | 2024-01-10 12:00    | Keyboard               | 2024-03-25 18:30   | Monitor               | 8

Дополнительные метрики

WITH customer_summary AS (
  SELECT
    customer_id,
    COUNT(*) as total_purchases,
    COUNT(DISTINCT DATE(purchase_date)) as purchase_days,
    MIN(purchase_date) as first_purchase_date,
    MAX(purchase_date) as last_purchase_date,
    SUM(amount) as total_spent,
    AVG(amount) as avg_purchase_amount
  FROM purchases
  GROUP BY customer_id
)
SELECT
  customer_id,
  total_purchases,
  purchase_days,
  first_purchase_date,
  last_purchase_date,
  EXTRACT(DAY FROM (last_purchase_date - first_purchase_date)) as days_since_first_purchase,
  ROUND(total_spent, 2) as total_spent,
  ROUND(avg_purchase_amount, 2) as avg_purchase_amount
FROM customer_summary
ORDER BY total_spent DESC;

Это дополнительно покажет:

• Сколько дней между первой и последней покупкой
• Общую сумму, потраченную клиентом
• Среднюю сумму одной покупки

Вариант с информацией о периоде активности

WITH customer_activity AS (
  SELECT
    customer_id,
    COUNT(*) as total_purchases,
    MIN(purchase_date) as first_purchase_date,
    MAX(purchase_date) as last_purchase_date,
    DATE_PART('day', MAX(purchase_date) - MIN(purchase_date)) as days_active,
    CASE
      WHEN MAX(purchase_date) >= CURRENT_DATE - INTERVAL '30 days' THEN 'Active'
      WHEN MAX(purchase_date) >= CURRENT_DATE - INTERVAL '90 days' THEN 'At Risk'
      ELSE 'Inactive'
    END as customer_status
  FROM purchases
  GROUP BY customer_id
)
SELECT
  customer_id,
  total_purchases,
  first_purchase_date,
  last_purchase_date,
  days_active,
  ROUND(total_purchases :: NUMERIC / GREATEST(days_active, 1), 2) as avg_purchases_per_day,
  customer_status
FROM customer_activity
ORDER BY last_purchase_date DESC;

Это поможет сегментировать клиентов по активности.