USE test;
 
DROP TABLE IF EXISTS users;

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `path` text NOT NULL,
  `type` char(1) NOT NULL,
  `level` int(11) NOT NULL DEFAULT '0',
  `infected_by` int(11) NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `inactive` int(1) NOT NULL DEFAULT '0',
  `session_key` varchar(255) NOT NULL DEFAULT '',
  `has_switched_sides` int(1) DEFAULT '0',
  UNIQUE KEY `id` (`id`),
  KEY `type` (`type`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 

INSERT INTO users (id, path) VALUES
   (1844,'/9200143/1844/')
 , (9200143,'/9200143/')
 , (2404,'/2404/')
 , (519470541,'/2404/519470541/')
 , (11536,'/2404/519470541/11536/')
 , (12286,'/502377204/12286/')
 , (502377204,'/502377204/')
;
 
SELECT u1.*
     , COUNT(*) cnt
  FROM users u1
  LEFT JOIN users u2
    ON u2.path LIKE CONCAT('/%',u1.id,'%/')
 GROUP BY u1.id
 ORDER BY cnt DESC
;
 
UPDATE users
  JOIN (
     SELECT u1.*
          , COUNT(*) cnt
       FROM users u1
       LEFT JOIN users u2
         ON u2.path LIKE CONCAT('/%',u1.id,'%/')
      GROUP BY u1.id
       ) v2
    ON v2.id=users.id
   SET users.level=v2.cnt
;
 
SELECT *
  FROM users
 ORDER BY level DESC
;