I don't have a clickhouse warehouse set up to test readily. If you could view the full query I could help hand-craft a version that moves the conditions to the WHERE clause and see if that runs on your clickhouse instance

Thanks for the super-fast response, Growth Book looks promising. What database would you recommend if ClickHouse might not be so tested? This limitation seems pretty big for our purposes too.

Can you try running the above directly on click house and see if it works?

Click house is one of our newest data sources and there usually a few edge cases to work out. Snowflake, Redshift, and Postgres are all well tested. We were just working with another user on BigQuery to fix some date edge cases. Luckily we only have about 5 unique sql queries that we run so it doesn't take that long to fix issues

Great to hear it isn't likely to be a huge challenge given the 5 queries 🙂

what version of click house are you using? It's really hard to tell from their documentation when certain features were added

oh, nevermind I see 21.3.15 in the error message

Can you try again with this one:

WITH __experiments as (
  SELECT
    user_id,
    anonymous_id,
    received_at as timestamp,
    experiment_id,
    variation_id,
    context_page_path as url,
    context_user_agent as user_agent
  FROM
    experiment_viewed
),
__experimentDates as (
  SELECT
    experiment_id,
    variation_id,
    dateTrunc('day', timestamp) as date,
    count(distinct anonymous_id) as users
  FROM
    __experiments
  WHERE
    timestamp > toDateTime('2020-08-12 11:51:08')
  GROUP BY
    experiment_id,
    variation_id,
    dateTrunc('day', timestamp)
),
__userThresholds as (
  SELECT
    experiment_id,
    variation_id,
    max(users) * 0.05 as threshold
  FROM
    __experimentDates
  WHERE
    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
  users > 200
  AND dateDiff('day', start_date, end_date) > 5
  AND dateDiff(
    'day',
    toDateTime('2020-08-12 11:51:08'),
    start_date
  ) > 2
ORDER BY
  experiment_id ASC,
  variation_id ASC

Getting a new error now 😅

rows.map is not a function

Query:

-- Past Experiments
WITH __experiments as (
  SELECT
    user_id,
    anonymous_id,
    received_at as timestamp,
    experiment_id,
    variation_id,
    context_page_path as url,
    context_user_agent as user_agent
  FROM
    experiment_viewed
),
__experimentDates as (
  SELECT
    experiment_id,
    variation_id,
    dateTrunc('day', timestamp) as date,
    count(distinct anonymous_id) as users
  FROM
    __experiments
  WHERE
    timestamp > toDateTime('2020-08-12 18:42:35')
  GROUP BY
    experiment_id,
    variation_id,
    dateTrunc('day', timestamp)
),
__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
  -- Skip experiments with fewer than 200 users since they don't have enough data
  users > 200 -- Skip experiments that are 5 days or shorter (most likely means it was stopped early)
  AND dateDiff('day', start_date, end_date) > 5 -- Skip experiments that start of the very first day since we're likely missing data
  AND dateDiff(
    'day',
    toDateTime('2020-08-12 18:42:35'),
    start_date
  ) > 2
ORDER BY
  experiment_id ASC,
  variation_id ASC

You know, I think it could be because of two things based on my searching: 1. React is trying to process a JSON response rather than an array (maybe because of how ClickHouse responds) 2. There are no results that have > 200 users and/or are spread out over more than 5 days, considering I'm using dummy data that didn't take that into account I think #2 is likely even if #1 is not the case. If so, a more friendly response might be good, if possible. I can work to ensure both conditions are met with the dummy data.

I think ClickHouse is returning tab separated values when I'm expected JSON

Just pushed a fix. Will take about 10-15 minutes to deploy to docker

I was specifying "format: json", but the nodejs client library wasn't adding the proper

x-clickhouse-format

header to the request. So clickhouse was returning tab-separated results and the library was expecting JSON and failing to parse it.