An admin view that shows you who is farming you

// 1. Earnings concentration — top earners against the median.
$top = $pdo->query(
    'SELECT user_id, SUM(units) AS earned, COUNT(*) AS events
     FROM ledger
     WHERE kind IN ("claim","mining","ptc") AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
     GROUP BY user_id ORDER BY earned DESC LIMIT 20'
)->fetchAll(PDO::FETCH_ASSOC);

// 2. One visitor key, several accounts — the clearest multi-account signal.
$shared = $pdo->query(
    'SELECT visitor_key, COUNT(DISTINCT user_id) AS accounts, GROUP_CONCAT(DISTINCT user_id) AS ids
     FROM activity
     WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
     GROUP BY visitor_key HAVING accounts > 2 ORDER BY accounts DESC LIMIT 20'
)->fetchAll(PDO::FETCH_ASSOC);

// 3. Signup velocity — bursts stand out against a normal day.
$signups = $pdo->query(
    'SELECT DATE_FORMAT(created_at, "%Y-%m-%d %H:00") AS hour, COUNT(*) AS n
     FROM users WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY)
     GROUP BY hour ORDER BY n DESC LIMIT 10'
)->fetchAll(PDO::FETCH_ASSOC);

// 4. Withdrawn far more than the activity log accounts for.
$mismatch = $pdo->query(
    'SELECT u.id, u.username,
            COALESCE(SUM(w.units), 0) AS withdrawn,
            (SELECT COUNT(*) FROM activity a WHERE a.user_id = u.id) AS actions
     FROM users u LEFT JOIN withdrawals w ON w.user_id = u.id AND w.status = "paid"
     GROUP BY u.id
     HAVING withdrawn > 0 AND actions < 20
     ORDER BY withdrawn DESC LIMIT 20'
)->fetchAll(PDO::FETCH_ASSOC);