Growthbook: filters change diff
16 removals
| Words removed | 43 |
| Total words | 526 |
| Words removed (%) | 8.17 |
219 lines
13 additions
| Words added | 43 |
| Total words | 526 |
| Words added (%) | 8.17 |
217 lines
-- 3 rows in set. Elapsed: 34.805 sec. Processed 580.47 million rows, 14.10 GB (16.68 million rows/s., 405.05 MB/s.)
-- 3 rows in set. Elapsed: 15.117 sec. Processed 505.99 million rows, 9.01 GB (33.47 million rows/s., 596.29 MB/s.)
-- Peak memory usage: 5.59 GiB.
-- Peak memory usage: 5.92 GiB.
-- RD1 (Rolling) (retention)
-- RD1 (Rolling) (retention)
WITH
WITH
__rawExperiment AS (
__rawExperiment AS (
SELECT
SELECT
device_id,
device_id,
time as timestamp,
time as timestamp,
event.property.exp_id as experiment_id,
event.property.exp_id as experiment_id,
event.property.var_id as variation_id
event.property.var_id as variation_id
FROM snowplow.distributed
FROM snowplow.distributed
WHERE event_type = 'se'
WHERE event_type = 'se'
-- AND app IN ('windy-web', 'windhub-web')
-- AND app IN ('windy-web', 'windhub-web')
AND event.category = 'feature_flag'
AND event.category = 'feature_flag'
AND event.action = 'experiment_view'
AND event.action = 'experiment_view'
AND event.label = 'growthbook'
AND event.label = 'growthbook'
AND experiment_id = 'windhub-map-legend'
AND timestamp >= toDateTime('2025-04-09 08:37:00', 'UTC')
AND timestamp <= toDateTime('2025-04-25 09:17:07', 'UTC')
),
),
__experimentExposures AS (
__experimentExposures AS (
-- Viewed Experiment
-- Viewed Experiment
SELECT
SELECT
e.device_id as device_id
e.device_id as device_id
, toString(e.variation_id) as variation
, toString(e.variation_id) as variation
, e.timestamp as timestamp
, e.timestamp as timestamp
FROM
FROM
__rawExperiment e
__rawExperiment e
WHERE
e.experiment_id = 'windhub-map-legend'
AND e.timestamp >= toDateTime('2025-04-09 08:37:00', 'UTC')
AND e.timestamp <= toDateTime('2025-04-25 09:17:07', 'UTC')
)
)
, __segment as (-- Segment (WindHub iOS)
, __segment as (-- Segment (WindHub iOS)
SELECT * FROM (
SELECT * FROM (
-- Fact Table (Snowplow)
-- Fact Table (Snowplow)
SELECT
SELECT
device_id as device_id,
device_id as device_id,
m.timestamp as date
m.timestamp as date
FROM(
FROM(
SELECT
SELECT
splitByChar('-', app)[1] as app_name,
splitByChar('-', app)[1] as app_name,
splitByChar('-', app)[2] as platform_name,
splitByChar('-', app)[2] as platform_name,
device_id,
device_id,
session_id,
session_id,
time as timestamp,
time as timestamp,
if(platform_name = 'web', path(page), page) as page,
if(platform_name = 'web', path(page), page) as page,
event_type,
event_type,
event.category as event_category,
event.category as event_category,
event.action as event_action,
event.action as event_action,
event.label as event_label
event.label as event_label
FROM snowplow.distributed
FROM snowplow.distributed
WHERE app_id NOT IN ('other', 'funnelfox')
WHERE app_id NOT IN ('other', 'funnelfox')
AND time >= '2025-01-01' AND time < now()
AND timestamp >= '2025-01-01' AND time < now()
AND app_name = 'windhub' AND platform_name = 'ios'
) m
) m
WHERE app_name = 'windhub' AND platform_name = 'ios'
) s )
) s )
, __experimentUnits AS (
, __experimentUnits AS (
-- One row per user
-- One row per user
SELECT
SELECT
e.device_id AS device_id
e.device_id AS device_id
, if(count(distinct e.variation) > 1, '__multiple__', max(e.variation)) AS variation
, if(count(distinct e.variation) > 1, '__multiple__', max(e.variation)) AS variation
, MIN(e.timestamp) AS first_exposure_timestamp
, MIN(e.timestamp) AS first_exposure_timestamp
FROM
FROM
__experimentExposures e
__experimentExposures e
JOIN __segment s ON (s.device_id = e.device_id)
JOIN __segment s ON (s.device_id = e.device_id)
WHERE s.date <= e.timestamp
WHERE s.date <= e.timestamp
GROUP BY
GROUP BY
e.device_id
e.device_id
),
),
__distinctUsers AS (
__distinctUsers AS (
SELECT
SELECT
device_id,
device_id,
toString('') AS dimension,
toString('') AS dimension,
variation,
variation,
first_exposure_timestamp AS timestamp,
first_exposure_timestamp AS timestamp,
dateTrunc('day', first_exposure_timestamp) AS first_exposure_date
dateTrunc('day', first_exposure_timestamp) AS first_exposure_date
, first_exposure_timestamp AS preexposure_end
, first_exposure_timestamp AS preexposure_end
, dateSub(hour, 336, first_exposure_timestamp) AS preexposure_start
, dateSub(hour, 336, first_exposure_timestamp) AS preexposure_start
FROM __experimentUnits
FROM __experimentUnits
)
)
, __metric as (-- Metric (RD1 (Rolling))
, __metric as (-- Metric (RD1 (Rolling))
SELECT
SELECT
device_id as device_id,
device_id as device_id,
1 as value,
1 as value,
m.timestamp as timestamp
m.timestamp as timestamp
FROM
FROM
(
(
SELECT
SELECT
splitByChar('-', app)[1] as app_name,
splitByChar('-', app)[1] as app_name,
splitByChar('-', app)[2] as platform_name,
splitByChar('-', app)[2] as platform_name,
device_id,
device_id,
session_id,
session_id,
time as timestamp,
time as timestamp,
if(platform_name = 'web', path(page), page) as page,
if(platform_name = 'web', path(page), page) as page,
event_type,
event_type,
event.category as event_category,
event.category as event_category,
event.action as event_action,
event.action as event_action,
event.label as event_label
event.label as event_label
FROM snowplow.distributed
FROM snowplow.distributed
WHERE app_id NOT IN ('other', 'funnelfox')
WHERE app_id NOT IN ('other', 'funnelfox')
AND time >= '2025-01-01' AND time < now()
AND time >= '2025-01-01' AND time < now()
AND timestamp >= toDateTime('2025-03-26 08:37:00', 'UTC') AND timestamp <= toDateTime('2025-04-25 09:17:07', 'UTC')
) m
) m
WHERE m.timestamp >= toDateTime('2025-03-26 08:37:00', 'UTC') AND m.timestamp <= toDateTime('2025-04-25 09:17:07', 'UTC')
)
)
, __userMetricJoin as (
, __userMetricJoin as (
SELECT
SELECT
d.variation AS variation,
d.variation AS variation,
d.dimension AS dimension,
d.dimension AS dimension,
d.device_id AS device_id,
d.device_id AS device_id,
if(m.timestamp >= dateAdd(hour, 24, d.timestamp)
if(m.timestamp >= dateAdd(hour, 24, d.timestamp)
AND m.timestamp <= toDateTime('2025-04-25 09:17:07', 'UTC')
AND m.timestamp <= toDateTime('2025-04-25 09:17:07', 'UTC')
, m.value, NULL) as value
, m.value, NULL) as value
FROM
FROM
__distinctUsers d
__distinctUsers d
LEFT JOIN __metric m ON (
LEFT JOIN __metric m ON (
m.device_id = d.device_id
m.device_id = d.device_id
)
)
)
)
, __userMetricAgg as (
, __userMetricAgg as (
-- Add in the aggregate metric value for each user
-- Add in the aggregate metric value for each user
SELECT
SELECT
umj.variation AS variation,
umj.variation AS variation,
umj.dimension AS dimension,
umj.dimension AS dimension,
umj.device_id,
umj.device_id,
COALESCE(MAX(umj.value), 0) as value
COALESCE(MAX(umj.value), 0) as value
FROM
FROM
__userMetricJoin umj
__userMetricJoin umj
GROUP BY
GROUP BY
umj.variation,
umj.variation,
umj.dimension,
umj.dimension,
umj.device_id
umj.device_id
)
)
, __userCovariateMetric as (
, __userCovariateMetric as (
SELECT
SELECT
d.variation AS variation,
d.variation AS variation,
d.dimension AS dimension,
d.dimension AS dimension,
d.device_id AS device_id,
d.device_id AS device_id,
COALESCE(MAX(value), 0) as value
COALESCE(MAX(value), 0) as value
FROM
FROM
__distinctUsers d
__distinctUsers d
JOIN __metric m ON (
JOIN __metric m ON (
m.device_id = d.device_id
m.device_id = d.device_id
)
)
WHERE
WHERE
m.timestamp >= d.preexposure_start
m.timestamp >= d.preexposure_start
AND m.timestamp < d.preexposure_end
AND m.timestamp < d.preexposure_end
GROUP BY
GROUP BY
d.variation,
d.variation,
d.dimension,
d.dimension,
d.device_id
d.device_id
)
)
-- One row per variation/dimension with aggregations
-- One row per variation/dimension with aggregations
SELECT
SELECT
m.variation AS variation,
m.variation AS variation,
m.dimension AS dimension,
m.dimension AS dimension,
COUNT(*) AS users,
COUNT(*) AS users,
SUM(COALESCE(m.value, 0)) AS main_sum,
SUM(COALESCE(m.value, 0)) AS main_sum,
SUM(POWER(COALESCE(m.value, 0), 2)) AS main_sum_squares
SUM(POWER(COALESCE(m.value, 0), 2)) AS main_sum_squares
,
,
SUM(COALESCE(c.value, 0)) AS covariate_sum,
SUM(COALESCE(c.value, 0)) AS covariate_sum,
SUM(POWER(COALESCE(c.value, 0), 2)) AS covariate_sum_squares,
SUM(POWER(COALESCE(c.value, 0), 2)) AS covariate_sum_squares,
SUM(COALESCE(m.value, 0) * COALESCE(c.value, 0)) AS main_covariate_sum_product
SUM(COALESCE(m.value, 0) * COALESCE(c.value, 0)) AS main_covariate_sum_product
FROM
FROM
__userMetricAgg m
__userMetricAgg m
LEFT JOIN __userCovariateMetric c
LEFT JOIN __userCovariateMetric c
ON (c.device_id = m.device_id)
ON (c.device_id = m.device_id)
GROUP BY
GROUP BY
m.variation
m.variation
, m.dimension
, m.dimension