Cattle counter based on events log

Given some event data that only consists of an id and a type of event (entry/exit) I'd like to compute a third column which could keep track of the current state of things (count), basically stating how many animals are present at any given time.

Example:

id   event
--------------
1    entry
2    entry
3    entry
4    entry
5    exit
6    exit
7    entry

I'd need an output similar to this (considering it starts empty)

id    event        count
-----------------------------
    1     entry        1
    2     entry        2
    3     entry        3
    4     entry        4
    5     exit         3
    6     exit         2
    7     entry        3

Where count holds keeps track of the actual number of animals after that event has been processed.

I've been trying to take some windowed approach, but after a couple of hours I just can't seem to nail it (I'm quite new to this yet).

Any help will be greatly appreciated.

My understanding of your requirements: For each event calculate a number based on the prior events (including the current one).
That is indeed possible by using a window function:

SELECT id, event, addend, SUM(addend) OVER (ORDER BY id ASC) AS count FROM (
  SELECT id, event, 
  CASE WHEN event = 'entry' THEN 1 WHEN event = 'exit' THEN -1 ELSE 0 END AS addend 
  FROM events
) AS events_with_addend

The type of event does not contain any information we can use in the aggregate sum, so we have to provide it (in the inner query).

Instead it is preferred to create a separate table event_types containing the name of the event as well as the change applied to count by it, then referencing event_typesfrom events (look up database normalization) and losing the inner query.

The actual window function then just sums up the addends of each row until (including) the current one (the window frame).

It is very possible this approach might lead to performance problems depending on the (growing) count of rows. If it does, have a look into materialized views

StackExchange Database Administrators Q#149115, answer score: 3