Топы
В компании замеряется такой показатель, как "30-дневная активная база". Для любого дня - это число клиентов за предыдущие 30 дней. Например, для 2022-01-01 - это число уникальных клиентов, совершивших визит за 30 дней до 2022-01-01, включая 2022-01-01. Для 2022-01-02 - это число уникальных клиентов, совершивших визит за 30 дней до 2022-01-02, включая 2022-01-02 и т.д.
Допустим, у вас есть таблица с чековыми данными по двум городам со следующими полями:
id - идентификатор чекаcityname - наименование городаdate - дата чекаorderid - id чекаclientid - id клиентаsales - сумма чека в рубляхДанные в таблице с 2022-01-01 по 2022-02-28.
Посчитайте подневную динамику 30-дневной активной базы по каждому городу, отсортируйте по городу и дате по возрастанию.
Поля в результирующей таблице: cityname, date, active_base.
Для каждой пары «город–дата» считаем количество уникальных клиентов, которые совершили хотя бы одну покупку (имели чек) за последние 30 дней включительно. Это можно сделать либо коррелированным подзапросом с COUNT(DISTINCT clientid) по диапазону дат, либо оконной функцией, если СУБД поддерживает COUNT(DISTINCT) в окне. Результат сортируем по cityname, затем по date по возрастанию.
Нужно посчитать 30-дневную активную базу: для каждой даты и города это количество уникальных клиентов, которые за последние 30 дней, включая текущую дату, совершили хотя бы одну покупку (то есть у них был хотя бы один чек в этот период).
Логика решения такая. Сначала очищаем данные от повторов на уровне дня: оставляем уникальные комбинации (cityname, date, clientid), чтобы каждый клиент за день учитывался один раз. Затем для каждой строки (город, дата) считаем, сколько уникальных клиентов данного города попадало в 30-дневное окно, заканчивающееся этой датой. Это и будет показатель active_base. В конце выбираем поля cityname, date, active_base и сортируем по городу и дате по возрастанию.
Отметьте свой прогресс