hi, i am using growthbook inside docker environment. i defined a test metric over trino db and when i ran the result experiment query, i got this error:
line 59:5: Column 'dimension' cannot be resolved
and here is my query:
-- test_metric_trino (binomial)
WITH __rawExperiment as (
SELECT
user_id as user_id,
timestamp as timestamp,
experiment_id as experiment_id,
variation_id as variation_id
FROM
hive.experiments.viewed_experiment
),
__experiment as (
-- Viewed Experiment
SELECT
e.user_id as user_id,
cast(e.variation_id as varchar) as variation,
e.timestamp as conversion_start,
e.timestamp + INTERVAL '72' hour as conversion_end
FROM
__rawExperiment e
WHERE
e.experiment_id = 'test_trino2'
AND e.timestamp >= from_iso8601_timestamp('2022-05-24T07
5900.000Z')
),
__metric as (
-- Metric (test_metric_trino)
SELECT
user_id as user_id,
1 as value,
m.timestamp as conversion_start,
m.timestamp as conversion_end
FROM
(
SELECT
user_id,
occurrence_timestamp as timestamp
FROM
hive.khabarchin.action_event
WHERE
event_id = 74
) m
WHERE
m.timestamp >= from_iso8601_timestamp('2022-05-24T07
5900.000Z')
),
__distinctUsers as (
-- One row per user/dimension
SELECT
e.user_id,
'All' as dimension,
(
CASE
WHEN count(distinct e.variation) > 1 THEN '__multiple__'
ELSE max(e.variation) END
) as variation,
MIN(e.conversion_start) as conversion_start,
MIN(e.conversion_end) as conversion_end
FROM
__experiment e
GROUP BY
dimension,
e.user_id
),
__userMetric as (
-- Add in the aggregate metric value for each user
SELECT
d.variation,
d.dimension,
1 as value
FROM
__distinctUsers d
JOIN __metric m ON (m.user_id = d.user_id)
WHERE
m.conversion_start >= d.conversion_start
AND m.conversion_start <= d.conversion_end
GROUP BY
variation,
dimension,
d.user_id
),
__overallUsers as (
-- Number of users in each variation
SELECT
variation,
dimension,
COUNT(*) as users
FROM
__distinctUsers
GROUP BY
variation,
dimension
),
__stats as (
-- Sum all user metrics together to get a total per variation/dimension
SELECT
variation,
dimension,
COUNT(*) as count,
AVG(value) as mean,
STDDEV(value) as stddev
FROM
__userMetric
GROUP BY
variation,
dimension
)
SELECT
s.variation,
s.dimension,
s.count,
s.mean,
s.stddev,
u.users
FROM
__stats s
JOIN __overallUsers u ON (
s.variation = u.variation
AND s.dimension = u.dimension
)