Rewrite the Trends 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 Trends insight.

Describe the solution you'd like

I'd like to rewrite the Trends 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]

Just got a +1 for these issues:

• Make it easier to compare a specific week day, month of the year with a previous instance #14991
• Custom start date when comparing to previous time period #12170

[mariusandra]

Issue reported with Monthly Active Users (purple line) showing 0 for the first day, if grouped by month or week. Grouping by day works.

[mariusandra]

Issue reported with Monthly Active Users (purple line) showing 0 for the first day, if grouped by month or week. Grouping by day works.

This now works in the HogQL version. However we don't show full months. Both "last 180 days" or "last 6 months" would currently start in the middle of August. I think it should take the full months in these cases. Internal thread.

[mariusandra]

This is live for everyone. Tracking follow up requests here: PostHog/meta#200