hi, i am using growthbook inside docker environment. i defined a test metric over trino db and when...
m

Mohamma Ali Bashari movahhed

about 3 years ago
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-24T075900.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-24T075900.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 )