title: Postgres: Average of monthly revenue sums using window
body: <p>I have a table of product/date transaction records and I would like to generate a summary table of monthly average product category revenue. The result would look like this:</p>
  
  <pre>
  ┌───────────────────┬────────────────────────┐
  │ product_category  │ avg_monthly_revenue    │
  ├───────────────────┼────────────────────────┤
  │ 5000              │       65003.04         │
  │ 5100              │        3301.95         │
  │ 5200              │       99029.00         │
  ...
  </pre>
  
  <p>I'm looking for the average of the category totals, and my naive attempt is returning average of the transaction revenue per month. </p>
  
  <p>What I want is the category monthly totals, and then the average of those months.</p>
  
  <p>I can do this with a subquery aggregate of sum() and then avg() functions, however I think this can be accomplished with a clean little select using <a href="https://www.postgresql.org/docs/current/static/tutorial-window.html" rel="nofollow noreferrer">Postgres SQL Window Functions</a>. Strangely, extensive googling has not yielded an applicable SO solution so I'm posting a question.</p>
  
  <p>Here is how the data is organized:</p>
  
  <pre>
  =#> \dt+ customer_transactions
          Table "customer_transactions"
  ┌─────────────────┬────────────────┬───────────┐
  │       Column    │      Type      │ Modifiers │
  ├─────────────────┼────────────────┼───────────┤
  │ order_day       │ date           │ not null  │
  │ product_id      │ text           │ not null  │
  │ product_category│ text           │ not null  │
  │ customer_id     │ bigint         │ not null  │
  │ revenue         │ numeric(38,14) │           │
  │ units           │ integer        │           │
  └─────────────────┴────────────────┴───────────┘
  
  =#> select * from customer_transactions limit 2;
  order_day   product_id   product_category   customer_id revenue
  24-MAR-16   A000BC2351   5000               44502       5.85
  02-NOV-16   A000CB0182   5200               99833       99.50
  ...
  </pre>
  
  <p>Here is my naive attempt using window functions:</p>
  
  <pre><code>/* this doesn't work right, it generates the monthly transaction average*/
  select      distinct product_category
              , avg(revenue) over (partition by date_trunc('month', order_day), product_category)  avg_cat_month_revenue
  
  from        customer_transactions
  </code></pre>
  
  <p></p>
  
  <pre>
  # result accurate but not desired monthly item-cust-category-trxn average:
  ┌──────────────────┬─────────────────────────┐
  │ product_category │   avg_cat_month_revenue │
  ├──────────────────┼─────────────────────────┤
  │ 5000             │       12.0143           │
  │ 5000             │       12.4989           │
  ...
  │ 5100             │       13.5472           │
  │ 5100             │       13.5643           │
  ...
  </pre>