I have a Postgres database. It has two tables that we’re concerned with right now. Users has a Name field and an ID field, and Events has a UserID field (FK to Users.ID), EventType field (typed as integer), and Time field (typed as datetime).

I want to find, for each user, the longest streak of consecutive days in which they have generated at least one event of type 3. The answer should return one row for each user, with the user’s name, the date on which the longest streak began, and the number of days in the streak. Joining the tables to get the name is trivial, but I can’t figure out any good way to determine the streaks. It feels like it should be an aggregate, but there aren’t any applicable aggregates that I can see.