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.    transaction_date TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP);(There are several other columns but theyre 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 Im 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 aremonth: 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 its 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 viewCREATE VIEW months AS    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;ExampleInserting the following dataINSERT 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
Summarizing income and expense information by month
sql;postgresql