Ok, so I have two tables.
locations:
+----+-----------+
| id | name |
+----+-----------+
| 1 | Location1 |
| 2 | Location2 |
| 3 | Location3 |
+----+-----------+
location_ratings
+----+-------------+-----------+--------+
| id | location_id | date | rating |
+----+-------------+-----------+--------+
| 1 | 1 | 4/7/2017 | 1 |
| 2 | 1 | 7/3/2017 | 2 |
| 3 | 1 | 9/9/2017 | 5 |
| 4 | 1 | 11/2/2017 | 4 |
| 5 | 2 | 1/3/2017 | 3 |
| 9 | 2 | 3/7/2017 | 1 |
| 12 | 3 | 2/7/2017 | 2 |
| 13 | 3 | 3/4/2017 | 4 |
| 15 | 3 | 10/1/2017 | 1 |
+----+-------------+-----------+--------+
Locations get rated at random times and they keep that rating until they are rated again. The rating of a location that has not yet been rated is considered to be 0. So for Location1, it was a 0 until 4/7/01, then it was a 1 until 7/3, etc, etc.
I need to calculate the number of locations per rating per month, where a locations rating for the month is whatever it's rating was on the 1st day of the month.
So basically I want to find the rating of each farm on the first day of each month. So using the example data above, this would be the rating for each farm on the first of every month:
+---------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+-----------+-----------+-----------+
| Location ID / Month | 1/1/2017 | 2/1/2017 | 3/1/2017 | 4/1/2017 | 5/1/2017 | 6/1/2017 | 7/1/2017 | 8/1/2017 | 9/1/2017 | 10/1/2017 | 11/1/2017 | 12/1/2017 |
+---------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+-----------+-----------+-----------+
| 1 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 2 | 2 | 5 | 5 | 5 |
| 2 | 0 | 3 | 3 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| 3 | 0 | 0 | 2 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 1 | 1 |
+---------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+-----------+-----------+-----------+
And then this would be the final result (number of locations at each rating per month):
+----------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+-----------+-----------+-----------+
| Rating / Month | 1/1/2017 | 2/1/2017 | 3/1/2017 | 4/1/2017 | 5/1/2017 | 6/1/2017 | 7/1/2017 | 8/1/2017 | 9/1/2017 | 10/1/2017 | 11/1/2017 | 12/1/2017 |
+----------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+-----------+-----------+-----------+
| 0 | 3 | 2 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 1 | 0 | 0 | 0 | 1 | 2 | 2 | 2 | 1 | 1 | 1 | 2 | 2 |
| 2 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 0 |
| 3 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 4 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 0 |
| 5 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 |
+----------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+-----------+-----------+-----------+
So I'm trying to figure out the best way to get to that final result. Not sure if I can basically do it all in a query or if I need to do some calculations after I pull all the data (I'm using Postgres and Ruby on Rails).I also should mention that I (eventually) need to be able to do this not just for month, but also for week, quarter, and year. Any suggestions would be greatly appreciated, thanks!
