Новые и сохраненные дубликаты в Bigquery

Question

Я использую BigQuery и DataStudio, чтобы показать график удержания в течение нескольких недель, и у меня возникают проблемы с этим, когда пользователь новый, а затем снова использует приложение в ту же неделю, когда он является новым и сохраняется в моих вычислениях, и я хотел бы, чтобы будь так, чтобы он был новичком только в первую неделю использования приложения, а затем, если он снова использует приложение в течение 2 недель, он "удерживается".

Это мой запрос:

SELECT
UserID,
DATE,
DATE_DIFF(DATE,PreviousSessionDATE, DAY) as DaysBetweenSessions,
(SELECT
 CASE
WHEN DaysBetweenSessions <= 14 THEN 'Retained'
WHEN DaysBetweenSessions >14 THEN 'Returned'
WHEN DaysBetweenSessions IS NULL AND FirstSessionDATE = DATE THEN 'New'
WHEN DaysBetweenSessions IS NULL THEN 'User has an old version without Retention Parameters'
END) as User_Type
FROM
app_project.analytics_*********.events_*
GROUP BY
1,2,3,4
ORDER BY
DATE DESC,
DaysBetweenSessions DESC,
1,2,3,4

Тогда результат, как правило, будет хорошим, за исключением случаев, когда пользователь использует приложение несколько раз и получает значение от 1 до 14 в качестве DaysBetweenSessions, а затем считается как новым и сохраненным в течение одной недели.

Затем в DataStudio я буду делать YEARWEEK(DATE) для визуализации в неделю и Count_Distinct(UserID) в качестве моих метрик.

Любые идеи о том, как я могу разобраться, чтобы новый пользователь считался новым только на первой неделе, даже если пользователь тренируется в течение этой недели?

Current output in BQ:
UserID     DATE        DaysBetweenSessions     User_Type
123        20180801    NULL                    "New"
123        20180801    0                       "Retained"

И желаемый выход

UserID     DATE        DaysBetweenSessions     User_Type
123        20180801    NULL                    "New"

Answer 1

Там может быть более аккуратный способ сделать это, но...

WITH CTE AS
(SELECT
UserID,
DATE,
DATE_DIFF(DATE,PreviousSessionDATE, DAY) as DaysBetweenSessions,
(SELECT
CASE
WHEN DaysBetweenSessions <= 14 THEN 'Retained'
WHEN DaysBetweenSessions >14 THEN 'Returned'
WHEN DaysBetweenSessions IS NULL AND FirstSessionDATE = DATE THEN 'New'
WHEN DaysBetweenSessions IS NULL THEN 'User has an old version without Retention 
Parameters'
END) as User_Type,
(SELECT
CASE
WHEN DaysBetweenSessions <= 7 THEN 0
WHEN DaysBetweenSessions >7 THEN 1
WHEN DaysBetweenSessions IS NULL AND FirstSessionDATE = DATE THEN 0
WHEN DaysBetweenSessions IS NULL THEN 2
END) as DaysBetween
FROM
app_project.analytics_*********.events_*
GROUP BY
1,2,3,4
ORDER BY
DATE DESC,
DaysBetweenSessions DESC,
1,2,3,4),

Result as 
(SELECT *, min(User_Type) OVER (PARTITION BY UserID, DaysBetween) minUser_Type
FROM CTE)

SELECT UserID,
DATE,
DaysBetweenSessions,
User_type 
FROM Result 
WHERE NOT (User_Type <> 'New' AND minUser_Type = 'New')

Вторая часть должна добавить измерение, которое является наименьшим по алфавиту User_Type за ту неделю (поэтому, если вы переименовали что-либо в алфавитном порядке меньше, чем "New", это не сработало бы, лучше использовать числа в идеале).

Последняя часть должна избавиться от тех, где на этой неделе было "New", но строка User_Type не "New".