SELECT COUNT( returning_visitors.per_ip ) AS count, AVG( returning_visitors.per_ip ) AS num_visits
FROM (
    SELECT COUNT( * ) AS per_ip
    FROM site_hits_unique
    WHERE site_hits_unique.site_id = ___INPUT___
    AND site_hits_unique.date >= CURDATE( ) - INTERVAL 30 DAY
    GROUP BY site_hits_unique.site_id, site_hits_unique.ip
    HAVING per_ip >1
) AS returning_visitors

SELECT id, COUNT( returning_visitors.per_ip ) as readers, AVG( returning_visitors.per_ip ) as avg_visits_pr
FROM sites
SUBQUERY-PER-ROW (
    SELECT COUNT( * ) AS per_ip
    FROM site_hits_unique
    WHERE site_hits_unique.site_id = sites.id
    AND site_hits_unique.date >= CURDATE( ) - INTERVAL 30 DAY
    GROUP BY site_hits_unique.site_id, site_hits_unique.ip
    HAVING per_ip > 1
) AS returning_visitors
WHERE sites.id IN (162888, 42705, 11412)

SELECT id, (
    SELECT COUNT( per_ip ) AS count, AVG( per_ip ) AS num_visits
    FROM (
        SELECT COUNT( * ) AS per_ip
        FROM site_hits_unique
        WHERE site_hits_unique.site_id = sites.id
        AND site_hits_unique.date >= CURDATE( ) - INTERVAL 30 DAY
        GROUP BY site_hits_unique.site_id, site_hits_unique.ip
        HAVING per_ip >1
    ) AS returning_visitors
) as (readers, avg_visits_pr)
FROM sites
WHERE sites.id IN (162888, 42705, 11412)

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 >= CURDATE( ) - INTERVAL 30 DAY
            GROUP BY  u.site_id, u.ip
            HAVING  per_ip > 1
    )  AS rv ON rv.site_id = s.id
    WHERE  s.id IN (162888, 42705, 11412)

site_hits_unique: INDEX(site_id, date, ip)

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 >= CURDATE( ) - INTERVAL 30 DAY
              AND  site_id IN (162888, 42705, 11412)
            GROUP BY  site_id, ip
            HAVING  per_ip > 1
    ) AS rv
    GROUP BY  site_id;

DELIMITER $$

CREATE PROCEDURE get_site_readers(
    IN input_site_id INT,
    OUT readers INT,
    OUT avg_visits_pr DOUBLE)
BEGIN

    SELECT COUNT( per_ip ), AVG( per_ip )
    INTO readers, avg_visits_pr
    FROM (
        SELECT COUNT( * ) AS per_ip
        FROM site_hits_unique
        WHERE site_id = input_site_id
        AND date >= CURDATE( ) - INTERVAL 30 DAY
        GROUP BY site_id, ip
        HAVING per_ip > 1
    ) AS returning_visitors;

END

DELIMITER $$

CREATE PROCEDURE update_site_readers() BEGIN
    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE _id BIGINT UNSIGNED;
    DECLARE cur CURSOR FOR SELECT id FROM sites;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;

    OPEN cur;

    eachSite: LOOP
        FETCH cur INTO _id;
        IF done THEN
            LEAVE eachSite;
        END IF;
        CALL get_site_readers(_id, @readers, @avg_visits_pr);
        UPDATE sites SET readers = @readers, avg_visits_pr = @avg_visits_pr WHERE id=_id;
    END LOOP eachSite;

    CLOSE cur;
END

CALL update_site_readers();