Q:

Running total... with a twist

I am trying to figure out the SQL to do a running total for a daily quota system.  The system works like this...
Each day a user gets a quota of 2 "consumable things".  If they use them all up, the next day they get another 2.  If they somehow over use them (use more than 2), the next day they still get 2 (they can't have a negative balance).  If they don't use them all, the remainder carries to the next day (which can carry to the next, etc...).
Here is a chart of data to use as validation.  It's laid out as quota for the day, amount used that day, amount left at the end of the day:
2 - 2 - 0
2 - 0 - 2
4 - 3 - 1
3 - 0 - 3
5 - 7 - 0
2 - 1 - 1
3 - 0 - 3
5 - 2 - 3
5 - 1 - 4
6 - 9 - 0

The SQL to start of with would be:
WITH t(x, y) AS (
  VALUES (2, '2013-09-16'),
              (0, '2013-09-17'),
              (3, '2013-09-18'),
              (0, '2013-09-19'),
              (7, '2013-09-20'),
              (1, '2013-09-21'),
              (0, '2013-09-22'),
              (2, '2013-09-23'),
              (1, '2013-09-24'),
              (9, '2013-09-25')
)

For the life of me, trying recursive with statements and window aggregates, I cannot figure out how to make it work (but I can certainly see the pattern).
It should be something like 2 - x + SUM(previous row), but I don't know how to put that in to SQL.

A:

Try creating custom aggregate function like:
CREATE FUNCTION quota_calc_func(numeric, numeric, numeric) -- carry over, daily usage and daily quota
RETURNS numeric AS 
$$
  SELECT GREATEST(0, $1 + $3 - $2);
$$
LANGUAGE SQL STRICT IMMUTABLE;

CREATE AGGREGATE quota_calc( numeric, numeric ) -- daily usage and daily quota
(
    SFUNC = quota_calc_func,
    STYPE = numeric,
    INITCOND = '0'
);

WITH t(x, y) AS (
  VALUES (2, '2013-09-16'),
              (0, '2013-09-17'),
              (3, '2013-09-18'),
              (0, '2013-09-19'),
              (7, '2013-09-20'),
              (1, '2013-09-21'),
              (0, '2013-09-22'),
              (2, '2013-09-23'),
              (1, '2013-09-24'),
              (9, '2013-09-25')
)
SELECT x, y, quota_calc(x, 2) over (order by y)
FROM t;

May contain bugs, haven't tested it.

