How do I create cohorts of users from month of first order, then count information about those orders in SQL?

I’m trying to use SQL to:

1. Create user cohorts by the month of their first order
2. Sum the total of all the order amounts bought by that cohort all-time
3. Output the cohort name (its month), the cohort size (total users who made first purchase in that month), total_revenue (all order revenue from the users in that cohort), and avg_revenue (the total_revenue divided by the cohort size)

Please see below for a SQL Fiddle, with sample tables, and the expected output:

http://www.sqlfiddle.com/#!15/b5937

Thanks!!

Users Table

+-----+---------+
| id  |  name   |
+-----+---------+
|   1 | Adam    |
|   2 | Bob     |
|   3 | Charles |
|   4 | David   |
+-----+---------+

Orders Table

+----+--------------+-------+---------+
| id |     date     | total | user_id |
+----+--------------+-------+---------+
|  1 | '2020-01-01' |   100 |       1 |
|  2 | '2020-01-02' |   200 |       2 |
|  3 | '2020-03-01' |   300 |       3 |
|  4 | '2020-04-01' |   400 |       1 |
+----+--------------+-------+---------+

Desired Output

+--------------+--------------+----------------+-------------+
|   cohort     | cohort_size  | total_revenue  | avg_revenue |
+--------------+--------------+----------------+-------------+
| '2020-01-01' |            2 |            700 |         350 |
| '2020-03-01' |            1 |            300 |         300 |
+--------------+--------------+----------------+-------------+

Answer

You can find the minimum date for every user and aggregate for them. Then you can aggregate for every such date:

with first_orders(user_id, cohort, total) as (
  select user_id, min(ordered_at), sum(total)
  from orders
  group by user_id
)
select to_char(date_trunc('month', fo.cohort), 'YYYY-MM-DD'), count(fo.user_id), sum(fo.total), avg(fo.total)
from first_orders fo
group by date_trunc('month', fo.cohort)