Trending sum over time

Question

I have a table (in Postgres 9.1) that looks something like this:

CREATE TABLE actions (
  user_id: INTEGER,
  date:    DATE,
  action:  VARCHAR(255),
  count:   INTEGER
)

For example:

    user_id    |    date    |     action   | count
---------------+------------+--------------+-------
             1 | 2013-01-01 | Email        |     1
             1 | 2013-01-02 | Call         |     3
             1 | 2013-01-03 | Email        |     3
             1 | 2013-01-04 | Call         |     2
             1 | 2013-01-04 | Voicemail    |     2
             1 | 2013-01-04 | Email        |     2
             2 | 2013-01-04 | Email        |     2

I would like to be able to view a user's total actions over time for a specific set of actions; for example, Calls + Emails:

  user_id  | date        |  count  
-----------+-------------+---------
         1 | 2013-01-01  |       1
         1 | 2013-01-02  |       4
         1 | 2013-01-03  |       7
         1 | 2013-01-04  |      11
         2 | 2013-01-04  |       2

The monstrosity that I've created so far looks like this:

SELECT
  date, user_id, SUM(count) OVER (PARTITION BY user_id ORDER BY date) AS count
FROM
  actions
WHERE
  action IN ('Call', 'Email') 
GROUP BY
  user_id, date, count;

Which works for single actions, but seems to break for multiple actions when they happen on the same day, for example instead of the expected 11 on 2013-01-04, we get 9:

    date    |      user_id | count
------------+--------------+-------
 2013-01-01 | 1            |     1
 2013-01-02 | 1            |     4
 2013-01-03 | 1            |     7
 2013-01-04 | 1            |     9 <-- should be 11?
 2013-01-04 | 2            |     2

Is it possible to tweak my query to resolve this issue? I tried removing the grouping on count, but Postgres doesn't seem to like that:

column "actions.count" must appear in the GROUP BY clause
or be used in an aggregate function
LINE 2:      date, user_id, SUM(count) OVER (PARTITION BY user...
                                ^

Answer 1

The table has a column named "count", and the expresion in the SELECT clause is aliased as "count", it is ambiguous.

Read documentation: http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-GROUPBY

In case of ambiguity, a GROUP BY name will be interpreted as an input-column name rather than an output column name.

That means, that your query does not group by "count" evaluated in the SELECT clause, but rather it groups by "count" values taken from the table.

This query gives expected results, see SQL Fiddle

SELECT date, user_id, count
from (
   Select date, user_id, 
          SUM(count) OVER (PARTITION BY user_id ORDER BY date) AS count
  FROM actions
  WHERE
    action IN ('Call', 'Email') 
) alias
GROUP BY
  user_id, date, count;

Answer 2

Asserts

It is unclear whether you want to sort by user_id or date

It is also unclear whether you want to include dates in the result list, for which there is no row in the base table. In this case, refer to this closely related answer:
PostgreSQL: running count of rows for a query 'by minute'

Repair names

First off, I am using this test table instead of your problematic table:

CREATE TEMP TABLE actions (
  user_id integer,
  thedate    date,
  action  text,
  ct   integer
);

Your use of reserved words and function names as identifiers (column names) is part of the problem.

Repair query

Combine aggregate and window functions

Since aggregate functions are applied first, your original query lumps the two rows found for user_id = 1 and thedate = '2013-01-04' into one. You have to multiply by count(*) to get the actual running count.

You can do this without subquery, since you can combine aggregate functions and window functions. Aggregate functions are applied first. You can even have a window functions over the result of aggregate functions.

SELECT thedate
     , user_id
     , sum(ct * count(*)) OVER (PARTITION BY user_id
                                ORDER BY thedate) AS running_ct
FROM   actions
WHERE  action IN ('Call', 'Email') 
GROUP  BY user_id, thedate, ct
ORDER  BY user_id, thedate;

Or simplify to:

...
 , sum(sum(ct)) OVER (PARTITION BY user_id
                      ORDER BY thedate) AS running_ct
...

This should also be the fastest of the solutions presented.

Here, the inner sum() is an aggregate function, while the outer sum() is a window function - over the result of the aggregate function.

Or use DISTINCT

Another way would to use DISTINCT or DISTINCT ON, since that is applied after window functions:

DISTINCT - this is possible, since running_ct is guaranteed to be the same in this case anyway, since all peers are summed at once for the default frame definition of window functions.

SELECT DISTINCT
       thedate
     , user_id
     , sum(ct) OVER (PARTITION BY user_id ORDER BY thedate) AS running_ct
FROM   actions
WHERE  action IN ('Call', 'Email')
ORDER  BY thedate, user_id;

Or simplify with DISTINCT ON:

SELECT DISTINCT ON (thedate, user_id)
...

->SQLfiddle demonstrating all variants.

Answer 3

This query produces the result you are looking for:

SELECT DISTINCT   
  date, user_id, SUM(count) OVER (PARTITION BY user_id ORDER BY date) AS count 
  FROM actions
WHERE
  action IN ('Call', 'Email');

The default window is already what you want, according to the official docs and the "DISTINCT" eliminates duplicate rows when both Emails and Calls happen on the same day.

See SQL Fiddle.