helpful-carpenter-3208
03/07/2022, 6:17 AMfuture-teacher-7046
helpful-carpenter-3208
03/07/2022, 6:20 AM-- Past Experiments
WITH __experiments as (
SELECT
user_id,
user_id as anonymous_id,
enroll_time as timestamp,
abtest_name as experiment_id,
enrolled as variation_id,
device_id
FROM
magic.mv_abtest_users
),
__experimentDates as (
SELECT
experiment_id,
variation_id,
dateTrunc('day', timestamp) as date,
count(distinct anonymous_id) as users
FROM
__experiments
WHERE
timestamp > toDateTime('2021-03-07 06:17:57')
GROUP BY
1,
2,
3
),
__userThresholds as (
SELECT
experiment_id,
variation_id,
-- It's common for a small number of tracking events to continue coming in
-- long after an experiment ends, so limit to days with enough traffic
max(users) * 0.05 as threshold
FROM
__experimentDates
WHERE
-- Skip days where a variation got 5 or fewer visitors since it's probably not real traffic
users > 5
GROUP BY
experiment_id,
variation_id
),
__variations as (
SELECT
d.experiment_id,
d.variation_id,
MIN(d.date) as start_date,
MAX(d.date) as end_date,
SUM(d.users) as users
FROM
__experimentDates d
JOIN __userThresholds u ON (
d.experiment_id = u.experiment_id
AND d.variation_id = u.variation_id
)
WHERE
d.users > u.threshold
GROUP BY
d.experiment_id,
d.variation_id
)
SELECT
*
FROM
__variations
WHERE
-- Experiment was started recently
dateDiff(
'day',
start_date,
toDateTime('2022-03-07 06:17:57')
) < 6
OR -- OR it ran for long enough and had enough users
(
dateDiff('day', start_date, end_date) >= 6
AND users > 100
AND -- Skip experiments at start of date range since it's likely missing data
dateDiff(
'day',
toDateTime('2021-03-07 06:17:57'),
start_date
) > 2
)
ORDER BY
experiment_id ASC,
variation_id ASC
click to minimize
500: Code: 215, e.displayText() = DB::Exception: Column `experiment_id` is not under aggregate function and
SELECT
user_id,
user_id as anonymous_id,
enroll_time as timestamp,
abtest_name as experiment_id,
enrolled as variation_id,
device_id
FROM
magic.mv_abtest_users
this is our settingfuture-teacher-7046
helpful-carpenter-3208
03/07/2022, 6:27 AMfuture-teacher-7046
GROUP BY 1,2,3
is causing it to break. Can you try switching that to use actual column names and running on clickhouse directly to see if it works?helpful-carpenter-3208
03/07/2022, 6:37 AMfuture-teacher-7046