I do a lot of looking at user behavior and want an easy way to order by the first event in a group of events per user. i.e.
User1 | Event1
User1 | Event2
User1 | Event3
User2 | Event1
User2 | Event2
User2 | Event3
Where Event1 for User1 is earlier than Event1 for User2.
I know I could make a table with each users’ first event and then join it, but I feel like there is a simpler way I am just not seeing. Any ideas?
2
Answers
Could be as simple as this if I understand correctly:
Or try the ORDER BY in reversed if you want all the Event 1s first
You could use the
first_value
window function, but in my experience it would probably be slower than the join to the derived table.In case you didn’t know, you can do the join to a dynamically defined aggregate table, it doesn’t have to be a permanent table.
It would be easier to offer example queries if your example data had included things like column names, or even all the necessary example columns.