https://www.growthbook.io/ logo
Join Slack
Powered by
This message was deleted.
# give-feedback
w
This message was deleted.
f
Are you sure it's that particular subquery? That one is identical whether or not you have an activation metric. The 
__distinctUsers
subquery is the one that incorporates the activation metric.
r
Did an other run. It indeed becomes slow earlier, with the distinctUsers. subquery. (where I first mentioned 46 seconds, it now takes 4 minutes).
@future-teacher-7046 When I add an aggregation step:
Copy code
__activatedUsers as (
  SELECT
    initial.user_pseudo_id,
    t0.conversion_start as conversion_start,
    t0.conversion_end as conversion_end
  FROM
    __experiment initial
    JOIN __activationMetric0 t0 ON (t0.user_pseudo_id = initial.user_pseudo_id)
  WHERE
    t0.conversion_start >= initial.conversion_start
    AND t0.conversion_start <= initial.conversion_end
),

__activatedAgg as (
  select
    user_pseudo_id,
    min(conversion_start) as conversion_start,
    min(conversion_end) as conversion_end
  from __activatedUsers
  group by 1
)
And then do a join on that CTE, the query runs fast again. From 4+ minutes to sub 10s
Copy code
__distinctUsers as (
  -- One row per user/dimension
  SELECT
    e.user_pseudo_id,
    cast('All' as string) as dimension,
    (
      CASE
      WHEN count(distinct e.variation) > 1 THEN '__multiple__'
      ELSE max(e.variation) END
    ) as variation,
    MIN(a.conversion_start) as conversion_start,
    MIN(a.conversion_end) as conversion_end
  FROM
    __experiment e
  JOIN __activatedAgg a using(user_pseudo_id)
  GROUP BY
    e.user_pseudo_id
)
Ok this works, too: aggregate in __activatedUsers directly. This makes inital.user_pseudo_id distinct, and prevents an explosive table later.
Copy code
__activatedUsers as (
  SELECT
    initial.user_pseudo_id,
    min(t0.conversion_start) as conversion_start,
    min(t0.conversion_end) as conversion_end,
  FROM
    __experiment initial
    JOIN __activationMetric0 t0 ON (t0.user_pseudo_id = initial.user_pseudo_id)
  WHERE
    t0.conversion_start >= initial.conversion_start
    AND t0.conversion_start <= initial.conversion_end
  group by 1
),
f
Thanks for looking into this. I think that 
group by
in 
__activatedUsers
could work at least some of the time. We recently added support for different Attribution Models and some of them need access to the individual activation events.
141 Views
Open in Slack
PreviousNext
Powered by