[Feedback] :pencil2: correction — Core action retention cohort by signup week using user_daily_engagement

Feedback (correction)

Question: Core action retention cohort by signup week using user_daily_engagement
Tool: query

Error

ClickHouse query failed: Unsupported JOIN ON conditions. Unexpected 'activity_date > signup_date'

Corrected SQL

SELECT toStartOfWeek(u.signup_date) AS signup_week, uniq(u.person_id) AS cohort_size, uniqIf(u.person_id, dateDiff('day', u.signup_date, e.activity_date) BETWEEN 1 AND 7) AS retained_d7, uniqIf(u.person_id, dateDiff('day', u.signup_date, e.activity_date) BETWEEN 1 AND 28) AS retained_d28 FROM (SELECT person_id, signup_date FROM semantic.user_daily_engagement WHERE signup_date >= toDate('2025-10-01') AND signup_date <= today() - 28 AND person_id != '' GROUP BY person_id, signup_date) AS u LEFT JOIN semantic.user_daily_engagement AS e ON u.person_id = e.person_id AND e.is_active = 1 GROUP BY signup_week ORDER BY signup_week

Corrected answer

ClickHouse does not support inequality conditions in JOIN ON clauses. Queries with ON e.activity_date > u.signup_date fail with: 'Unsupported JOIN ON conditions. Unexpected activity_date > signup_date'. For retention queries, join only on person_id equality, then use dateDiff() in SELECT with uniqIf() to compute retention windows. Pattern: LEFT JOIN ... ON u.person_id = e.person_id then uniqIf(person_id, dateDiff('day', signup_date, activity_date) BETWEEN 1 AND 28)

Notes

This is a fundamental ClickHouse limitation that caused multiple query retries. The query tool instructions already mention to prefer subqueries over CTEs but do not mention the JOIN inequality constraint. This should be added explicitly: ClickHouse JOIN ON only supports equality conditions. Use dateDiff() in SELECT/WHERE instead of inequality in ON.

Feedback ID: 3890bdf3-ade4-48b4-906e-986621f77886 | Recorded at 2026-03-11T00:52:45.072Z

Locate the query tool instructions referenced in the issue and review how JOIN guidance is currently written. Add the documented ClickHouse equality-only JOIN constraint and the dateDiff/SELECT workaround described here; done when the instructions clearly prevent inequality conditions in JOIN ON clauses.