使用postgres查找Rails中用户的连续最长天数

class User < ApplicationRecord
  def self.longest_streak
    query = <<-SQL
      SELECT COUNT(*) AS streak
      FROM (
        SELECT date_trunc('day', created_at) AS date,
               ROW_NUMBER() OVER (ORDER BY date_trunc('day', created_at)) AS rn
        FROM users
        WHERE created_at >= current_date - interval '30 days'
      ) AS subquery
      GROUP BY date_trunc('day', created_at) - rn
      ORDER BY streak DESC
      LIMIT 1
    SQL

    result = ActiveRecord::Base.connection.execute(query)
    result.first['streak'].to_i
  end
end