I made myself a simple budgeting application. Each transaction is stored in the
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 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. “
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.
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?
CREATE 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;
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
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