Re: slow self-join query

Robert Poor <rdpoor@gmail.com> wrote:
> among users that follow leader 321, who are the most widely
> followed leaders?", or more formally, find all the users that are
> followers of user 321 (via inner join on F1)  Of those users,
> tally up their leaders so we know which leaders are most popular.

It sounds like you're looking for something like this:

SELECT leader_id, count(*) as count
  FROM user_associations x
  WHERE exists
        (
          SELECT * FROM user_associations y
            WHERE y.follower_id = x.follower_id
              AND y.leader_id = 321
        )
  GROUP BY leader_id
;

>  create_table "user_associations", :force => true do |t|
>   t.integer  "follower_id"
>   t.integer  "leader_id"
>  end

I don't really know what that means.  In the future, it would make
things easier on those who are trying to help if you either post the
SQL form or go into psql and type `\d tablename`.

-Kevin