1

invoice

 created_at | amount | status | 
------------+--------+---------
 2016-06-01 |   50   |   2    |     
 2016-05-30 |   150  |   2    |     
 2016-04-29 |   32   |   2    |     
 2016-03-28 |   24   |   2    |     
 2016-01-27 |   11   |   2    |     

In the results I want month, the SUM of the amount with status as 2 Grouped by Month, % Month Over Month change in Sum of amount, % Change in Current Sum of Amount over the Past 4 Months.

The result should include month even when the sum amount is 0, that's why I had to use generate_series and do a left join.

Here is the query I wrote to get the desired result.

SELECT
  month,
  amount,
  Round(100.00 * (amount - last_amount) / NullIf(last_amount, 0), 2) || ' %' AS monthly_delta,
  Round(100.00 * (amount - (Avg(amount) OVER (
    ORDER BY month DESC
    ROWS BETWEEN 1 FOLLOWING AND 4 FOLLOWING
  ))) / NullIf(Avg(amount) OVER (
    ORDER BY month DESC
    ROWS BETWEEN 1 FOLLOWING AND 4 FOLLOWING
  ), 0), 2) || ' %' AS "past 4 months moving avg delta"
FROM (
  SELECT 
    Date_Trunc('month', m) AS month,
    Coalesce(Sum(invoice.amount), 0.00) AS amount,
    Lag(Sum(invoice.amount), 1) OVER (ORDER BY Date_Trunc('month', m)) AS last_amount
  FROM Generate_Series(
    Date_Trunc('month', current_date) - interval '2 year',
    Date_Trunc('month', current_date),
    '1 month'
  ) m
  LEFT JOIN invoice
    ON Date_Trunc('month', invoice.created_at) = m
    AND invoice.status = 2
  GROUP BY month
  ORDER BY month DESC
) tt;

How can I simplify this query so it's easy to understand ( mainly the 4 month average )

  • 3
    Not sure yet about simplifying the query itself but adding some indentation to it might make it easier to read/understand. – Andriy M May 26 '16 at 6:50
  • Using Common table expressions usually makes a complex query easier to read and understand. But, as far as I know, in Postgres CTEs affect the query optimizer. – Vladimir Baranov May 30 '16 at 6:52
  • Using CTE may slow down the query. you should care about that. how about creating function or view ? – Sahap Asci May 31 '16 at 5:54
  • Have you tried selecting one month in one query an joining it 4 times for different months? It would help if the problem is in window functions and nested queries. – Stoleg May 31 '16 at 11:52

Your Answer

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

Browse other questions tagged or ask your own question.