select
        pe.id as person_id,
        pl.name as player_name,
        pl.id as player_id,
        pl.className as class_name,
        ranks.name as rank_name,
        (case when num_raids.raid_count is not null then num_raids.raid_count el
se 0 end) as raid_count
        #{outer_query_sql}
from
players pl,
people pe,
(
        select
                pe.id as person_id
                #{earned_query_sql}
        from
                people pe,
                players pl,
                zones z,
                zone_groups zg,
                raids r
        left join per_raid_dkp rdkp on rdkp.player_id = pl.id
        where
                pe.id = pl.person_id
                and
                r.id = rdkp.raid_id
                and
                r.zone_id = z.id
                and
                z.zone_group_id = zg.id
        group by
                pe.id
        order by
                pe.id,
                zg.id
) earned
left join
(
        select tmpcount.person_id as person_id, count(tmpcount.raid_id) as raid_
count from (
          select
            pe.id as person_id,
            raids.id as raid_id
          from
            players pl,
            people pe,
            raids,
            per_raid_dkp rdkp
          where
            pl.person_id = pe.id
            and
            rdkp.player_id = pl.id

            /* minimum dkp */
            and
            rdkp.dkp >= (select max(dkp) * #{MIN_DKP_PERCENT} from per_raid_dkp
where id = rdkp.id)

            and
            rdkp.raid_id = raids.id
            and
            raids.start_time >= '#{start_date.to_formatted_s(:db)} 06:00'

            /* Only include raids that should count towards the weekly count */
            and
            raids.add_to_weekly_count = 1

          group by
            pe.id, raids.id
        ) tmpcount group by tmpcount.person_id
) num_raids on (earned.person_id = num_raids.person_id)
left join
(
        select
                person_id
                #{spent_query_sql}
        from
                spent_dkp sdkp
        group by
                person_id
) spent on (earned.person_id = spent.person_id)
left join ranks on pe.rank_id = ranks.id

WHERE
  earned.person_id = pe.id
  AND
  pe.id = pl.person_id
  AND
  pl.alternate = 0
  AND
  pe.active = 1
ORDER BY
  #{sortby}
END