Rewrite the Retention Insight in HogQL

[mariusandra]

Is your feature request related to a problem?

This is part of the push to rewrite all insights in HogQL. I've created separate issues for each insight type, so that users can subscribe here and get notified when it's done.

This issue is about the Retention insight.

Describe the solution you'd like

I'd like to rewrite the Retention Insight in HogQL.

• Most product analytics insights are built in a bespoke way in our "legacy" Python codebase. This code produces ClickHouse SQL as its output. It's hard to extend, as every new feature (e.g. group support, multiple breakdowns) needs to be manually added to each insight. It's hard to extend the current system, and there are many bugs and unimplemented edge case features.

• We've built HogQL, which takes care of implementation details, and lets users focus on writing queries. Every platform feature added to HogQL (e.g. group support, new aggregations, etc) will automatically be available in every insight everywhere.

• We're now rewriting our python codebase to output HogQL instead of ClickHouse SQL. This will fix whole classes of bugs, make the system more robust, and allow the end user to directly modify the generated HogQL SQL for further analysis.

• This migration is also a blocker for the removal of a lot of legacy frontend code, which is often causing bugs.

Additional context

See the latest todo list in the megaissue: PostHog/meta#130

[mariusandra]

Feature request: add filters to the start and target events (just requested again)

[mariusandra]

New requests:

• Retention insight: Show the average retention for each period #19543
• Percentages in the CSV not the total numbers (zendesk)
• See top level averages on retention graphs. Last 90 days, i'd want to see the average D1-30 retention at the top of the table across all the cohorts.

[mariusandra]

The HogQL retention insight has now been launched to everyone 🎉

New things out of the gate:

• The "Edit SQL directly" button next to retention insights (in the ... menu)
• The "Explore" button in the persons modals, opening a view that lets you filter the resulting data further

Going to keep this issue open until we can also add the new requested features.

[mariusandra]

Closing this. New requests moved to PostHog/meta#200