skip to Main Content

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


  1. Could be as simple as this if I understand correctly:

    SELECT * 
    FROM your_table_name
    ORDER BY user_field, event_field;
    

    Or try the ORDER BY in reversed if you want all the Event 1s first

    SELECT * 
    FROM your_table_name
    ORDER BY event_field, user_field;
    
    Login or Signup to reply.
  2. You could use the first_value window function, but in my experience it would probably be slower than the join to the derived table.

    select userid, event from foo order by first_value(event) over (partition by userid order by something) 
    

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search