БЛОГ НА HUSL


Преобразование маркированных интервалов времени в хронологию событий

Автор вопроса: Vitalizzare

Любопытная задача по SQL с технического собеседования в Amazon. Статистика успешных решений на тренажёре - менее 5% на 9 октября 2025 г. Суть: нужно преобразовать последовательность интервалов, каждый из которых имеет определённый маркер, в последовательность событий, отражающих смену маркеров в момент времени.

Условие

Дана таблица subscriptions с историей всех подписочных периодов для каждого клиента. Напишите SQL-запрос, который преобразует историю подписок в хронологический список событий.

Пример исходных данных:

customer_id membership_start_date membership_end_date membership_status
115 2020-01-01 2020-02-15 Free
115 2020-02-15 2020-03-15 Paid
115 2020-03-15 2020-04-01 Non-member
115 2020-04-01 2020-10-01 Paid

Основные правила переходов:

Free → Paid: 'Convert'
Paid → Free: 'ReverseConvert'
Paid → Non-member: 'Cancel'
Free → Non-member: 'Cancel'
Non-member → Paid: 'ColdStart'
Non-member → Free: 'WarmStart'
Paid → Paid: 'Renewal'
Free → Free: 'Renewal'

Пример результата:

customer_id change_date event
115 2020-01-01 WarmStart
115 2020-02-15 Convert
115 2020-03-15 Cancel
115 2020-04-01 ColdStart
115 2020-10-01 Cancel

Поля в результирующей таблице: customer_id, change_date, event.

Отсортируйте результат в порядке возрастания по customer_id, а затем по change_date.

Исходные данные

CREATE TABLE subscriptions (
    customer_id INT NOT NULL,
    membership_start_date DATE NOT NULL,
    membership_end_date DATE NOT NULL,
    membership_status VARCHAR NOT NULL CHECK (membership_status IN ('Paid', 'Free', 'Non-member'))
);

INSERT INTO subscriptions (customer_id, membership_start_date, membership_end_date, membership_status)
VALUES
(6234, '2020-01-20', '2020-04-12', 'Paid'),
(8112, '2020-02-10', '2020-03-10', 'Free'),
(9543, '2020-02-15', '2020-04-01', 'Non-member'),
(7890, '2020-03-01', '2020-08-16', 'Paid'),
(8112, '2020-03-10', '2020-05-10', 'Paid'),
(9543, '2020-04-01', '2020-05-01', 'Free'),
(5501, '2020-04-01', '2020-09-15', 'Non-member'),
(6234, '2020-04-12', '2020-07-12', 'Non-member'),
(9543, '2020-05-01', '2020-07-16', 'Paid'),
(8112, '2020-05-10', '2020-08-25', 'Non-member'),
(6234, '2020-07-12', '2020-09-28', 'Paid'),
(9543, '2020-07-16', '2020-10-16', 'Non-member'),
(7890, '2020-08-16', '2020-11-16', 'Non-member'),
(8112, '2020-08-25', '2021-02-01', 'Non-member'),
(5501, '2020-09-15', '2020-10-15', 'Free'),
(6234, '2020-09-28', '2021-09-28', 'Paid'),
(5501, '2020-10-15', '2020-11-15', 'Free'),
(9543, '2020-10-16', '2021-02-08', 'Paid'),
(5501, '2020-11-15', '2021-04-10', 'Non-member'),
(7890, '2020-11-16', '2021-02-13', 'Paid'),
(8112, '2021-02-01', '2021-04-20', 'Paid'),
(9543, '2021-02-08', '2021-03-24', 'Non-member'),
(7890, '2021-02-13', '2021-07-16', 'Non-member'),
(9543, '2021-03-24', '2021-04-24', 'Free'),
(5501, '2021-04-10', '2021-05-10', 'Free'),
(8112, '2021-04-20', '2021-07-20', 'Non-member'),
(9543, '2021-04-24', '2021-05-24', 'Free'),
(5501, '2021-05-10', '2022-05-10', 'Paid'),
(9543, '2021-05-24', '2021-08-24', 'Paid'),
(7890, '2021-07-16', '2022-07-16', 'Paid'),
(8112, '2021-07-20', '2021-08-20', 'Free'),
(8112, '2021-08-20', '2021-11-20', 'Paid'),
(9543, '2021-08-24', '2021-11-11', 'Non-member'),
(6234, '2021-09-28', '2021-12-28', 'Non-member'),
(9543, '2021-11-11', '2021-12-11', 'Free'),
(8112, '2021-11-20', '2022-01-20', 'Paid'),
(9543, '2021-12-11', '2022-01-11', 'Free'),
(6234, '2021-12-28', '2022-02-28', 'Free'),
(9543, '2022-01-11', '2022-04-11', 'Non-member'),
(8112, '2022-01-20', '2022-04-18', 'Non-member'),
(6234, '2022-02-28', '2022-05-14', 'Paid'),
(9543, '2022-04-11', '2022-06-25', 'Non-member'),
(8112, '2022-04-18', '2022-09-02', 'Non-member'),
(5501, '2022-05-10', '2022-08-10', 'Non-member'),
(6234, '2022-05-14', '2022-09-12', 'Non-member'),
(9543, '2022-06-25', '2022-07-25', 'Free'),
(7890, '2022-07-16', '2022-08-16', 'Free'),
(9543, '2022-07-25', '2022-08-25', 'Free'),
(5501, '2022-08-10', '2023-01-05', 'Non-member'),
(7890, '2022-08-16', '2022-11-16', 'Paid'),
(9543, '2022-08-25', '2023-02-15', 'Non-member'),
(8112, '2022-09-02', '2022-10-02', 'Free'),
(6234, '2022-09-12', '2022-12-12', 'Non-member'),
(8112, '2022-10-02', '2022-11-02', 'Free'),
(8112, '2022-11-02', '2023-02-05', 'Non-member'),
(7890, '2022-11-16', '2023-03-05', 'Non-member'),
(6234, '2022-12-12', '2023-01-12', 'Free'),
(5501, '2023-01-05', '2023-02-05', 'Free'),
(6234, '2023-01-12', '2023-02-12', 'Free'),
(5501, '2023-02-05', '2023-03-05', 'Free'),
(8112, '2023-02-05', '2023-03-05', 'Free'),
(6234, '2023-02-12', '2023-06-15', 'Paid'),
(9543, '2023-02-15', '2023-03-15', 'Free'),
(7890, '2023-03-05', '2023-04-05', 'Free'),
(5501, '2023-03-05', '2023-06-05', 'Non-member'),
(8112, '2023-03-05', '2023-06-19', 'Non-member'),
(9543, '2023-03-15', '2023-04-15', 'Free'),
(7890, '2023-04-05', '2023-08-08', 'Paid'),
(9543, '2023-04-15', '2023-05-15', 'Free'),
(9543, '2023-05-15', '2023-06-15', 'Free'),
(5501, '2023-06-05', '2023-07-05', 'Free'),
(9543, '2023-06-15', '2023-07-15', 'Free'),
(6234, '2023-06-15', '2023-09-15', 'Non-member'),
(8112, '2023-06-19', '2024-06-19', 'Paid'),
(5501, '2023-07-05', '2023-10-05', 'Paid'),
(9543, '2023-07-15', '2023-09-28', 'Paid'),
(7890, '2023-08-08', '2023-11-20', 'Non-member'),
(6234, '2023-09-15', '2023-10-15', 'Free'),
(9543, '2023-09-28', '2024-03-01', 'Non-member'),
(5501, '2023-10-05', '2024-01-05', 'Paid'),
(6234, '2023-10-15', '2024-01-15', 'Paid'),
(7890, '2023-11-20', '2023-12-20', 'Free'),
(7890, '2023-12-20', '2024-02-23', 'Non-member'),
(5501, '2024-01-05', '2024-04-22', 'Non-member'),
(6234, '2024-01-15', '2024-04-03', 'Non-member'),
(7890, '2024-02-23', '2024-03-23', 'Free'),
(9543, '2024-03-01', '2024-04-01', 'Free'),
(7890, '2024-03-23', '2025-03-23', 'Paid'),
(9543, '2024-04-01', '2025-04-01', 'Paid'),
(6234, '2024-04-03', '2024-05-03', 'Free'),
(5501, '2024-04-22', '2024-05-22', 'Free'),
(6234, '2024-05-03', '2024-06-03', 'Free'),
(5501, '2024-05-22', '2025-05-22', 'Paid'),
(6234, '2024-06-03', '2024-09-03', 'Paid'),
(8112, '2024-06-19', '2024-07-19', 'Free'),
(8112, '2024-07-19', '2024-10-19', 'Paid'),
(6234, '2024-09-03', '2025-01-03', 'Paid'),
(8112, '2024-10-19', '2025-01-19', 'Paid'),
(6234, '2025-01-03', '2025-02-03', 'Free'),
(8112, '2025-01-19', '2025-02-19', 'Free'),
(6234, '2025-02-03', '2025-05-03', 'Non-member'),
(8112, '2025-02-19', '2025-03-19', 'Free'),
(7890, '2025-03-23', '2025-06-23', 'Non-member'),
(9543, '2025-04-01', '2025-07-01', 'Non-member'),
(5501, '2025-05-22', '2025-06-22', 'Free');

В чём вопрос?

Я могу решить эту задачу парой способов как любитель. Но мне хотелось бы увидеть размышления людей на опыте. Какие вещи вы поставите под сомнение? На что хочется ругаться? Какие идеи считаете правильным привлечь? И в конечном счете - в каком виде вы как интервьюер приняли бы ответ?


Источник

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

Автор решения: Akina 
WITH cte1 AS (
  SELECT *, 
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY membership_start_date) rn
  FROM subscriptions
)
SELECT COALESCE(t1.customer_id, t2.customer_id) customer_id,
       COALESCE(t1.membership_start_date, t2.membership_end_date) change_date,
       actions.event
FROM cte1 t1
FULL JOIN cte1 t2 ON t1.customer_id = t2.customer_id
                 AND t1.rn = t2.rn + 1
JOIN actions ON COALESCE(t1.membership_status, 'Non-member') = actions.status_to
            AND COALESCE(t2.membership_status, 'Non-member') = actions.status_from
ORDER BY 1, 2;

Примечание: в таблицу изменений статусов добавлена запись

('Non-member','Non-member','Non-member')

Эта запись будет использоваться в случаях:

  1. В исходных данных имеется две записи со статусом 'Non-member' подряд (а такие случаи присутствуют);
  2. Самая первая по датам запись имеет статус 'Non-member' (и такие тоже есть).

online fiddle

UPDATE

CREATE TEMPORARY TABLE actions (status_from VARCHAR, status_to VARCHAR, event VARCHAR);
INSERT INTO actions (status_from, status_to, event) VALUES
('Free','Paid','Convert'),
('Paid','Free','ReverseConvert'),
('Paid','Non-member','Cancel'),
('Free','Non-member','Cancel'),
('Non-member','Paid','ColdStart'),
('Non-member','Free','WarmStart'),
('Non-member','Non-member','Non-member'),
('Paid','Paid','Renewal'),
('Free','Free','Renewal');
SELECT * FROM actions;
→ Ссылка
Автор решения: Gtufc

Не знаю от чего отталкивались при составлении условия задачи, но на 3 сутки пришел к методу исключения. Возможно, исходя из правильного ответа и условия задачи, теперь и можно где-то уловить суть исключения. За основу взял решение выше, просто исключил Non-member:

with actions as (
    SELECT 'Free' status_from ,'Paid' status_to, 'Convert' event
    union all 
    SELECT 'Paid','Free','ReverseConvert'
    union all 
    SELECT 'Paid','Non-member','Cancel'
    union all 
    SELECT 'Free','Non-member','Cancel'
    union all 
    SELECT 'Non-member','Paid','ColdStart'
    union all 
    SELECT 'Non-member','Free','WarmStart'
    union all 
    SELECT 'Non-member','Non-member','Non-member'
    union all 
    SELECT 'Paid','Paid','Renewal'
    union all 
    SELECT 'Free','Free','Renewal' )
, cte1 AS (
    SELECT *, 
        ROW_NUMBER() OVER (
            PARTITION BY customer_id ORDER BY membership_start_date) rn
    FROM subscriptions)
select * 
from (
    SELECT
        COALESCE(t1.customer_id, t2.customer_id) customer_id,
        COALESCE(t1.membership_start_date, t2.membership_end_date) change_date,
        actions.event
    FROM cte1 t1
    FULL JOIN cte1 t2 ON t1.customer_id = t2.customer_id
                     AND t1.rn = t2.rn + 1
    JOIN actions ON COALESCE(t1.membership_status, 'Non-member') = actions.status_to
                AND COALESCE(t2.membership_status, 'Non-member') = actions.status_from) 
where event != 'Non-member'
ORDER BY 1, 2;
→ Ссылка

licensed under cc by-sa 3.0 with attribution.