1

I'm trying to measure a specific user behaviour of users of every day after they sign up to a given service.

This is an example of a table I'd like to create

enter image description here

Where the date (x axis) is the day the user joined and the days after joining is user.created_at + x days.

The values are the % of the users of that cohort who undertook a specific action on x days after they signed up.

Is there a way to generate this purely with Postgresql?

AMB
  • 221
  • 3
  • 8
  • Do you want it explicitly for these dates (sept 28 - oct 3) -> fixed columns? Or you want it 5 days into the past no matter what date currently is -> columns are dynamic? Do you want it for more than 5 days? – S-Man Oct 04 '18 at 16:51
  • What's the table structure? Anyway, it seems you need a "pivot" query. I think they are called "crosstab" in PostgreSQL. – The Impaler Oct 04 '18 at 17:25
  • Possible duplicate of [PostgreSQL Crosstab Query](https://stackoverflow.com/questions/3002499/postgresql-crosstab-query) – The Impaler Oct 04 '18 at 17:26
  • It would be for the previous 7 or 28 days (for example). As for the table structure, I have a `users` table and a `posts` table each with `created_at` dates. The query is to see what % of users created a post x days after sign up. – AMB Oct 05 '18 at 09:53

0 Answers0