How to fold an event stream into a table? I'm trying to experiment a pure-postgres event sourced projection, and I'd like to represent a stream of events as a table:
imagine this flow of events:
┌───────┬───────────────────────┬───────┬──────────────────────────────┐
│ order |        type           │ topic │           payload            │
├───────┼───────────────────────┼───────┼──────────────────────────────┤
│ 1     | user-registered       │ users │ {&quot;name&quot;: &quot;john&quot;}             │
│ 2     | user-changed-password │ users │ {&quot;name&quot;: &quot;john&quot;, &quot;activation_token&quot;: &quot;1234&quot;} │
│ 3     | user-registered       │ users │ {&quot;name&quot;: &quot;bob&quot;}              │
│ 4     | user-banned           │ users │ {&quot;name&quot;: &quot;bob&quot;}              │
└───────┴───────────────────────┴───────┴──────────────────────────────┘


I'd like to create a view of the current state of active users containing:
┌──────┬──────────────────┐
│ name │ activation_token │
├──────┼──────────────────┤
│ john │ 1234             │
└──────┴──────────────────┘

In this example, there is only 1 active user because the second user has been banned.
So my question is: how could I &quot;fold&quot; the events table to end up with the active_users table ?
I tried to use case expressions but it's so hard to merge records:
create view users as
    select case type
        when 'user-registered' then (payload-&gt;&gt;'name', null, true)
        when 'user-changed-password' then (null, payload-&gt;&gt;'activation_token', true)
        when 'user-banned' then (null, null, false)
    end from es.events where topic = 'users'
    order by &quot;order&quot; asc
;
assuming the are no duplicate events you can do this.

create view "users" as
  select payload-&gt;&gt;'name'
  from es.events
  group by 1
  having
      sum ( case type 
            when 'user-registered' then 1
            when 'user-banned' then -1 
            end ) &gt; 0
union 
  select payload-&gt;&gt;'activation_token'
  from es.events
  where type='user-changed-password';