0

I was playing around with Cohort Analysis in Google Analytics and was trying to figure out how to write it in SQL. There is a pretty extensive answer to this from about 5 years ago for MySQL here: Cohort analysis in SQL. However, I was wondering if with the new window functions in mysql8, this might have gotten significantly more easy. For example, to reproduce this:

enter image description here

I would assume the data is something like:

  • timestamp
  • user_id
  • user_creation_timestamp

And it seems like the date interval is per week over the past 6 weeks. Does the window/partitioning functions make this any easier now?

David542
  • 104,438
  • 178
  • 489
  • 842
  • 1
    Looks more like a pivot to me, and the lead answer uses conditional aggregation which is a pivoting technique. Window functions don't necessarily make pivoting any easier as far as I'm aware; ultimately a pivot is turning a lot of rows and a few columns into fewer rows and more columns, whereas window functions essentially perform grouping/aggregation on a DataSet but don't affect the row count. You could use window functions in a pivot, probably to augment the original data set with extra columns of info that is aggregate in some specific way but it doesn't make pivoting any easier or harder – Caius Jard Jul 28 '20 at 05:02
  • Please provide sample data and desired results *in the question* -- preferably with a SQL/db fiddle. A question should stand on its own. – Gordon Linoff Jul 28 '20 at 12:15

0 Answers0