MySQL Correlated Subquery (with multiple columns) per row
Is this what you are looking for?

SELECT  s.id,
        COUNT( rv.per_ip ) as readers,
        AVG( rv.per_ip ) as avg_visits_pr
    FROM  sites AS s
    JOIN  
    (
        SELECT  u.site_id,
                COUNT( * ) AS per_ip
            FROM  site_hits_unique AS u
            WHERE  u.date &gt;= CURDATE( ) - INTERVAL 30 DAY
            GROUP BY  u.site_id, u.ip
            HAVING  per_ip &gt; 1 
    )  AS rv ON rv.site_id = s.id
    WHERE  s.id IN (162888, 42705, 11412)


Indexes:

site_hits_unique: INDEX(site_id, date, ip)


Or maybe this:

SELECT  site_id,
        COUNT(DISTINCT ip) as readers,
        COUNT(*) / COUNT(DISTINCT ip) as avg_visits_pr
    FROM  
    (
        SELECT  site_id, ip, COUNT(*) AS per_ip
            FROM  site_hits_unique
            WHERE  date &gt;= CURDATE( ) - INTERVAL 30 DAY
              AND  site_id IN (162888, 42705, 11412)
            GROUP BY  site_id, ip
            HAVING  per_ip &gt; 1 
    ) AS rv
    GROUP BY  site_id;