I have a database table of user interactions. I would like to create groups of users based on the time & place of interaction. That is, if users are interacting at roughly the same time (e.g., 2 minute window) in the same location, I would consider them a group. The groups do not need to be mutually exclusive, but they do need to be exhaustive. Every user interaction belongs in one or more groups.

I've done something similar to this in the past with python and a

The group ids are non-consecutive, but you can see that there are 7 different groups, with each group having at least one element. Some rows belong to many groups, but there's no row without a group.

CodePudding user response：

You're close. Just add this to your existing select statement to assign group ids to user interactions. It results in 8 groups, but that's because it's allowing groups to be mutually inclusive such that users can cross with other users multiple times a day (permitting -2 minutes diff). It also allows users to cross with themselves irrespective of whether they also crossed with a different user.

Since you're still thinking about the edge cases, I think this is a good/flexible start and should get you past the grouping hurdle. Make sure you're data was deduped before the join

dense_rank() over (partition by a.intxn_date, a.loc_id order by a.user_id) as group_id