def topreq_data_hits_available(days):
    """Evaluates toprequesters by the number of hits available as an average
    over the time period.

    Keyword arguments:
    days -- number of days to look back

    """
    start_time = datetime.date.today() - datetime.timedelta(int(days))

    # We are only interested in records having hits_posted > 0, thus only such
    # records will appear on the list and max(start_time) should be available at
    # all times.
    return list(query_to_dicts("""
        SELECT
            h.requester_id,
            h.requester_name,
            count(DISTINCT hitgroup.group_id) as "projects",
            coalesce(round(CAST (sum(hitgroup.grp_hits) as NUMERIC), 0), 0) as hits,
            coalesce(sum(hitgroup.grp_hits * h.reward), 0) as reward,
            max(hitgroup.grp_last_posted) as "last_posted"
        FROM
            main_hitgroupcontent h
            LEFT JOIN main_requesterprofile p
                ON h.requester_id = p.requester_id
            LEFT JOIN (
            SELECT
                mv.group_id,
                coalesce(avg(mv.hits_available), 0) as "grp_hits",
                max(mv.start_time) as "grp_last_posted"
            FROM (
                SELECT group_id, hits_available, start_time
                FROM hits_mv
                WHERE
                    start_time > '{0}'
                ) mv
            GROUP BY mv.group_id
            ) hitgroup
                ON h.group_id = hitgroup.group_id
        WHERE
            coalesce(p.is_public, true) = true
        GROUP BY h.requester_id, h.requester_name
        ORDER BY reward DESC
        LIMIT 1000;""".format(start_time.isoformat())))
dataset/ETHPy150Open ipeirotis/Mturk-Tracker/app/mturk/toprequesters/reports.py/topreq_data_hits_available/original