How can I implement this in active record?

I would like to transfer this question to active record:

The SQL statement I would like to transfer:

SE开发者_运维知识库LECT user.name, user.subject
FROM user
INNER JOIN (
    SELECT name, COUNT(1) AS occurrences
    FROM user
    GROUP BY name
  ) AS user_occurrences
  ON user.name = user_occurrences.name
ORDER BY user_occurrences.occurrences DESC, user.name ASC, user.subject ASC
LIMIT 4

How to write this SQL statement?

I can join table, but how can I create a new table in the active record? Thank you.

The Active Record class has very limited capabilites. Other than writing the sub-query inside the join method, there's not much that you can do.