I made myself a simple budgeting application. Each transaction is stored in the transactions table:

CREATE TABLE transactions (
    -- Foreign key into the "categories" table, not shown here. (In reality this
    -- has a NOT NULL constraint instead of a default, but this makes the
    -- example data easier to enter.)
    category INTEGER DEFAULT 1,
    -- Number of cents. Positive values represent income and negative values are
    -- expenses.
    amount INTEGER NOT NULL,
    -- The date this transaction occurred.

(There are several other columns but they’re not relevant to my question, which deals with a view of some summary information.) At the beginning of each month my application adds an income transaction for each of my spending categories; these use positive amounts. Each time I spend money throughout the month I add a transaction with a negative amount. If money is left over in a category at the end of the month I’m allowed to use it in the next month, so I can check my categories’ balances just by grouping all of the transactions by category and summing the amounts.

I wrote the following view to try to summarize my spending habits each month (this time ignoring my spending categories and lumping all of my transactions together). The columns it generates are

  • month: The month in ISO 8601 format, e.g. “2015-08”.
  • carryover: The amount of money that was available before the month started (and before the income transactions at the beginning of the month, which in practice get timestamps about 20 milliseconds after the start of the month).
  • filled: The total amount of all income transactions that occurred during the month.
  • spent: The total amount of all expense transactions that occurred during the month, expressed now as a positive number.
  • pct_of_fill: spent / filled.
  • pct_of_available: spent / (filled + carryover).

The view I wrote seems to work correctly but it’s grotesque. The WITH clauses seem redundant and there is a lot of duplication in the SELECT portion of the main query. How can I rewrite this view in a more idiomatic way?

Months view

    WITH filled AS (
            SELECT date_trunc('month', transaction_date) AS month,
                sum(amount) AS filled
            FROM transactions
            WHERE amount > 0
            GROUP BY month),
        spent AS (
            SELECT date_trunc('month', transaction_date) AS month,
                sum(amount) AS spent
            FROM transactions
            WHERE amount < 0
            GROUP BY month),
        months AS (
            SELECT DISTINCT date_trunc('month', transaction_date) AS month
            FROM transactions),
        cumulative AS (
            SELECT month,
                sum(amount) AS s
            FROM transactions
            LEFT JOIN months ON transaction_date < months.month
            GROUP BY month)
    SELECT to_char(filled.month, 'YYYY-MM') AS month,
        coalesce(sum(cumulative.s), 0) AS carryover,
        sum(filled.filled) AS filled,
        abs(sum(spent.spent)) AS spent,
        round(100 * abs(sum(spent.spent))/sum(filled.filled), 1) AS pct_of_fill,
        round(100 * abs(sum(spent.spent))/(coalesce(sum(cumulative.s), 0)+sum(filled.filled)), 1) AS pct_of_available
    FROM filled
    LEFT JOIN spent ON filled.month = spent.month
    LEFT JOIN cumulative ON filled.month = cumulative.month
    GROUP BY filled.month
    ORDER BY filled.month;


Inserting the following data

INSERT INTO transactions(amount, transaction_date) VALUES
    (10000, '2015-06-01T00:00:00.02'),
    (-3000, '2015-06-05'),
    (-1000, '2015-06-06'),
    (-4000, '2015-06-21'),
    (10000, '2015-07-01T00:00:00.02'),
    (-2500, '2015-07-03'),
    (-1000, '2015-07-05'),
    (-7000, '2015-07-11'),
    (10000, '2015-08-01T00:00:00.02'),
    (-1500, '2015-08-03');

gives the following output for months:

  month  | carryover | filled | spent | pct_of_fill | pct_of_available 
 2015-06 |         0 |  10000 |  8000 |        80.0 |             80.0
 2015-07 |      2000 |  10000 | 10500 |       105.0 |             87.5
 2015-08 |      1500 |  10000 |  1500 |        15.0 |             13.0

Postgresql has a money data type specifically for storing currency. I understand that you're already avoiding floating point errors by storing cents, but using money might end up being nicer for display purposes at least (and in case you gain or lose more than 21 million dollars).


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.