Monthly streak display using SQL CTE in Rails

How to make a "streak display" like 750words.com

![image](https://user-images.githubusercontent.com/3908/107557540-5f6ec880-6b9f-11eb-90b9-928b4601523e.png)

The database can do the heavy lifting for this kind of thing. A CTE (Common Table Expression) in a `WITH` clause to give me the set of days of the month that I would fill in with the completion data using a `LEFT JOIN` to make sure I got rows back even when there was no corresponding data on the right side.

```ruby
# app/models/streak.rb
Streak = Struct.new(:user, :date) do
  # If you're wondering why we didn't use class extension syntax of Struct see the following link
  # https://tiagoamaro.com.br/2016/03/05/superclass-mismatch-structs-and-unicorn/
  SQL = "WITH dates(d) AS (
            SELECT generate_series(
              (date ?)::timestamp,
              (date ?)::timestamp,
              interval '1 day')
            )
            SELECT dates.d::date created_at, count(e.id) count FROM dates
            LEFT JOIN entries e on dates.d::date = e.created_at::date AND e.user_id = ?
            GROUP BY dates.d::date
            ORDER BY dates.d::date"


  def calendar
    Entry.find_by_sql([SQL, date.beginning_of_month, date.end_of_month, user.id])
  end
end
```

I'm knowingly abusing ActiveRecord by shoving the data I want to represent into `Entry` objects. I can be kinder to my future self and other maintainers by subsequently wrapping the returned objects into something more descriptive like `StreakMonth` or whatever.

obie-fernandez
February 10, 2021