1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
|
import clickhouse from '@/lib/clickhouse';
import { CLICKHOUSE, PRISMA, runQuery } from '@/lib/db';
import prisma from '@/lib/prisma';
import type { QueryFilters } from '@/lib/types';
export interface RetentionParameters {
startDate: Date;
endDate: Date;
timezone?: string;
}
export interface RetentionResult {
date: string;
day: number;
visitors: number;
returnVisitors: number;
percentage: number;
}
export async function getRetention(
...args: [websiteId: string, parameters: RetentionParameters, filters: QueryFilters]
) {
return runQuery({
[PRISMA]: () => relationalQuery(...args),
[CLICKHOUSE]: () => clickhouseQuery(...args),
});
}
async function relationalQuery(
websiteId: string,
parameters: RetentionParameters,
filters: QueryFilters,
): Promise<RetentionResult[]> {
const { startDate, endDate, timezone } = parameters;
const { getDateSQL, getDayDiffQuery, getCastColumnQuery, rawQuery, parseFilters } = prisma;
const unit = 'day';
const { filterQuery, joinSessionQuery, cohortQuery, queryParams } = parseFilters({
...filters,
websiteId,
startDate,
endDate,
timezone,
});
return rawQuery(
`
WITH cohort_items AS (
select
min(${getDateSQL('website_event.created_at', unit, timezone)}) as cohort_date,
website_event.session_id
from website_event
${cohortQuery}
${joinSessionQuery}
where website_event.website_id = {{websiteId::uuid}}
and website_event.created_at between {{startDate}} and {{endDate}}
${filterQuery}
group by website_event.session_id
),
user_activities AS (
select distinct
website_event.session_id,
${getDayDiffQuery(getDateSQL('created_at', unit, timezone), 'cohort_items.cohort_date')} as day_number
from website_event
join cohort_items
on website_event.session_id = cohort_items.session_id
where website_id = {{websiteId::uuid}}
and created_at between {{startDate}} and {{endDate}}
),
cohort_size as (
select cohort_date,
count(*) as visitors
from cohort_items
group by 1
order by 1
),
cohort_date as (
select
c.cohort_date,
a.day_number,
count(*) as visitors
from user_activities a
join cohort_items c
on a.session_id = c.session_id
group by 1, 2
)
select
c.cohort_date as date,
c.day_number as day,
s.visitors,
c.visitors as "returnVisitors",
${getCastColumnQuery('c.visitors', 'float')} * 100 / s.visitors as percentage
from cohort_date c
join cohort_size s
on c.cohort_date = s.cohort_date
where c.day_number <= 31
order by 1, 2`,
queryParams,
);
}
async function clickhouseQuery(
websiteId: string,
parameters: RetentionParameters,
filters: QueryFilters,
): Promise<RetentionResult[]> {
const { startDate, endDate, timezone } = parameters;
const { getDateSQL, rawQuery, parseFilters } = clickhouse;
const unit = 'day';
const { filterQuery, cohortQuery, queryParams } = parseFilters({
...filters,
websiteId,
startDate,
endDate,
timezone,
});
return rawQuery(
`
WITH cohort_items AS (
select
min(${getDateSQL('created_at', unit, timezone)}) as cohort_date,
session_id
from website_event
${cohortQuery}
where website_id = {websiteId:UUID}
and created_at between {startDate:DateTime64} and {endDate:DateTime64}
${filterQuery}
group by session_id
),
user_activities AS (
select distinct
website_event.session_id,
toInt32((${getDateSQL('created_at', unit, timezone)} - cohort_items.cohort_date) / 86400) as day_number
from website_event
join cohort_items
on website_event.session_id = cohort_items.session_id
where website_id = {websiteId:UUID}
and created_at between {startDate:DateTime64} and {endDate:DateTime64}
),
cohort_size as (
select cohort_date,
count(*) as visitors
from cohort_items
group by 1
order by 1
),
cohort_date as (
select
c.cohort_date,
a.day_number,
count(*) as visitors
from user_activities a
join cohort_items c
on a.session_id = c.session_id
group by 1, 2
)
select
c.cohort_date as date,
c.day_number as day,
s.visitors as visitors,
c.visitors returnVisitors,
c.visitors * 100 / s.visitors as percentage
from cohort_date c
join cohort_size s
on c.cohort_date = s.cohort_date
where c.day_number <= 31
order by 1, 2`,
queryParams,
);
}
|