Как рассчитать количество дней между покупками SQL?

RFM в работе маркетолога является незаменимым инструментом анализа данных и получению инсайтов по работе с клиентами.

Представим себе данные RFM анализа.

Исходные данные для примера (разделитель запятая):

date,Userid,transactionid,revenue
2020-01-01,1,1,1279
2020-01-02,2,2,438
2020-01-03,2,37,724
2020-01-03,3,3,1113
2020-01-03,8,47,811
2020-01-03,14,27,1268
2020-01-04,4,4,601
2020-01-04,12,48,617
2020-01-04,14,28,801
2020-01-04,19,38,763

Уникальным значением в строке является:

• transactionid — идентификатор транзакции
• revenue — средний чек
• Userid — идентификатор уникального клиента
• date — дата формирования транзакции

А теперь нам нужно с вами узнать, сколько дней у пользователей прошло между первой и второй транзакцией, а потом между второй и третьей и т.д. Нужно это для того, чтобы мы могли построить распределение дней между двумя транзакциями и получить возможность влиять на поведение пользователей путём своевременных коммуникаций, направленных на стимулирование продаж.

Решить эту задачу можно при помощи SQL довольно специфическим образом:

WITH
num_buy_table as ( select 1 as num_buy) --- вы можете заменить 1 на любое число. Значение 1 покажет вам количество дней по пользователю между первой и второй покупкой. Если нужно между второй и третьей, то ставьте число 2.
SELECT
userid
,difdate
,number_buy from
(
SELECT UserId
,date ProductName,
LEAD(date) OVER (PARTITION BY UserId ORDER BY date) AS l1,
DATE_DIFF ( LEAD(date) OVER (PARTITION BY UserId ORDER BY date),date, day) AS difdate,
ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY date) as number_buy
FROM ВАША_ТАБЛИЦА
WHERE УСЛОВИЕ_ФИЛЬТРАЦИИ_ЕСЛИ_НУЖНО
ORDER BY userid
)
WHERE number_buy in (select num_buy from num_buy_table)
ORDER BY userid -- сортировка по желанию

В результате данного запроса вы получите результирующую таблицу со следующими столбцами:

• Userid — идентификатор пользователя
• Difdate — количество дней между двумя транзакциями, которое мы указали в первой строке запроса
• Number_buy — порядковый номер покупки, которую мы сравниваем из первой строки нашего запроса

Искренне надеюсь, что этот запрос облегчит вашу жизнь.