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

Nobody has claimed this yet.

## Feedback (correction)

**Question:** Core action retention cohort by signup week
**Tool:** `query`

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

### Corrected SQL
```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 28) AS retained_d28 FROM (SELECT person_id, signup_date FROM semantic.user_daily_engagement WHERE signup_date >= toDate('2025-10-01') 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
For retention cohort analysis on user_daily_engagement, ClickHouse does not support inequality conditions in JOIN ON clauses (e.g. e.activity_date > u.signup_date). Use a subquery approach with dateDiff() in the SELECT/HAVING instead. Also avoid CTEs with WITH...AS — use subqueries in FROM clauses instead.

### Notes
ClickHouse does not allow inequality conditions in JOIN ON. For retention queries, join on person_id only, then use dateDiff() in SELECT to filter to specific retention windows. Also: aggregate functions like min() cannot be used in WHERE clauses — compute them in a subquery first.

---
_Feedback ID: `58d7907a-8755-498e-aa00-9c5bd26acf15` | Recorded at 2026-03-11T00:44:07.229Z_

Start with the query tool and reproduce the retention cohort request using the SQL and ClickHouse error in this issue. Compare the generated query with the corrected SQL, including its subquery structure and dateDiff-based filtering. Done means retention cohort queries avoid unsupported inequality JOIN conditions, CTEs, and aggregate functions in WHERE clauses.

Written by the indexing model from the issue text.