gistfile1.sql

SELECT row_number FROM (
  SELECT row_number() OVER (ORDER BY created_at NULLS LAST) AS row_number, id
  FROM posts
  WHERE user_id = 123
  ORDER BY created_at
) posts
WHERE posts.id = 3
;

class Post < ActiveRecord::Base
  def other_posts
    Post.where(user_id: user_id).where.not(id: id)
  end

  def nested_id
    other_posts.pluck(:id).select {|other_id| other_id < self.id }.count + 1
  end
end

Not ordering by created_at since it's the same order as the id (auto increment)