I have a database for rounds of golf. I want to see how many rounds each user has per month for the last year.

To do that I created this view `last_12_months` with this code

```sql
SELECT date_part('month'::text, dates.date) AS month,
    date_part('year'::text, dates.date) AS year
FROM ( SELECT (generate_series((now() - '1 year'::interval), now(), '1 mon'::interval))::date AS date) dates;

```

| month | year |
| --- | --- |
| 1 | 2020 |
| 2 | 2020 |
| and so on to ... |  |
| 12 | 2020 |

The summary view for counting the rounds is very simple also `rounds_count_by_users`

| user\_id | month | year | count\_all |
| --- | --- | --- | --- |
| 1 | 1 | 2020 | 15 |
| 1 | 3 | 2020 | 12 |
| 1 | 5 | 2020 | 10 |
| 2 | 4 | 2020 | 7 |
| 2 | 8 | 2020 | 6 |
| 2 | 9 | 2020 | 3 |

Now for what I want, querying for each user with `left outer join` is quite simple with

```sql
select * 
from last_12_months
left outer join rounds_count_by_users
  on last_12_months.month = rounds_count_by_users.month
  and last_12_months.year = rounds_count_by_users.year
  and user_id = 1

```

Which gives me all the months even when the user has no played rounds. What I would like however is to be able to do this for every user and make a materialized view for easy querying. Is there a nice and easy way of doing this? To be clear this is the final table I want.

This query doesn't work at least, that much I know.

```sql
select * 
from last_12_months
left outer join rounds_count_by_users
  on last_12_months.month = rounds_count_by_users.month
  and last_12_months.year = rounds_count_by_users.year
where user_id = 1

```

| user\_id | month | year | count\_all |
| --- | --- | --- | --- |
| 1 | 1 | 2020 | 15 |
| 1 | 2 | 2020 | null |
| 1 | 3 | 2020 | 12 |
| 1 | 4 | 2020 | null |
| 1 | 5 | 2020 | 10 |
| 1 | 6 | 2020 | null |
| 1 | 7 | 2020 | null |
| 1 | 8 | 2020 | null |
| 1 | 9 | 2020 | null |
| 1 | 10 | 2020 | null |
| 1 | 11 | 2020 | null |
| 1 | 12 | 2020 | null |
| 2 | 1 | 2020 | null |
| 2 | 2 | 2020 | null |
| 2 | 3 | 2020 | null |
| 2 | 4 | 2020 | 7 |
| 2 | 5 | 2020 | null |
| 2 | 6 | 2020 | null |
| 2 | 7 | 2020 | null |
| 2 | 8 | 2020 | 6 |
| 2 | 9 | 2020 | 3 |
| 2 | 10 | 2020 | null |
| 2 | 11 | 2020 | null |
| 2 | 12 | 2020 | null |

I made an [SQL Fiddle](http://sqlfiddle.com/#!17/cecc34/1) for this (slightly different values but same schema)

*PS: I know about table aliases and data modeling and that stuff. My question is strictly about how to achieve the final result.*