MMN ranking query

Question

Hello, I have the following problem: I made a micro-social network for managers, where initially the registration is made only by invitation, thus "measuring" the influence of each one. My problem is in the ranking that currently carries out the count of invitations accepted by each person and the one that has more invitations accepted, obviously is at the top of the ranking:

      SELECT 
          u.usr_id, b.ref_id, u.nome, b.c_hab, COUNT(*) AS qnt
      FROM
          tbl_user AS u
      INNER JOIN
          tbl_user b ON u.usr_id = b.ref_id
      WHERE
          b.c_hab = 1
      GROUP BY u.nome
      ORDER BY qnt DESC;

My problem arose when I was asked to count the direct invitations, that is, the manager sent to fulano1 (we will call level 1), the invitations that fulano1 sent (level 2) and so on. It’s confusing to explain, but basically a system of Multilevel Marketing and count the invitations in the ranking, only I have no idea how to accomplish this...

Follow the users table for further details:

      CREATE TABLE `tbl_user` (
        `usr_id` smallint(6) NOT NULL,
        #Aqui é onde são armazenados as IDs de quem convidou você
        `ref_id` smallint(6) DEFAULT NULL,
        `nome` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
        `email` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
        `dt_nascimento` date DEFAULT NULL,
        `cel` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
        `endereco` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
        `numero` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
        `senha` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
        `foto` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
        `token` char(10) COLLATE utf8_unicode_ci DEFAULT NULL,
        `c_hab` char(1) COLLATE utf8_unicode_ci DEFAULT '0',
        `nivel` char(1) COLLATE utf8_unicode_ci DEFAULT '1',
        `candidato` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
        `dt_cad` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
      ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

If you can provide a possible solution, I would like to thank you in advance!

Answer 1

I do not intend for this answer to solve 100% your problem, but I believe that what you should do, for the purposes of registration, is to keep an exclusive table for invitations.

Your invitation table would have reference to the user who made the invitation and the user who received the invitation. In addition to another reference to the invitation table itself, this last reference would be for you to know the level of the indication.

Follows the diagram

---

User table

CREATE TABLE IF NOT EXISTS `tbl_user` (
  `usr_id` SMALLINT(6) NOT NULL,
  `nome` VARCHAR(100) NULL,
  `email` VARCHAR(45) NULL,
  `dt_nascimento` DATE NULL,
  PRIMARY KEY (`usr_id`))
ENGINE = InnoDB

Invitation table

CREATE TABLE IF NOT EXISTS `convites` (
  `id` INT NOT NULL,
  `dt_indicacao` DATETIME NULL,
  `convites_id` INT NULL,
  `tbl_user_enviou` SMALLINT(6) NOT NULL,
  `tbl_user_recebeu` SMALLINT(6) NOT NULL,
  PRIMARY KEY (`id`, `convites_id`, `tbl_user_enviou`, `tbl_user_recebeu`),
  INDEX `fk_convites_tbl_user2_idx` (`tbl_user_enviou` ASC),
  INDEX `fk_convites_tbl_user1_idx` (`tbl_user_recebeu` ASC),
  INDEX `fk_convites_convites1_idx` (`convites_id` ASC),
  CONSTRAINT `fk_convites_tbl_user2`
    FOREIGN KEY (`tbl_user_enviou`)
    REFERENCES `tbl_user` (`usr_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_convites_tbl_user1`
    FOREIGN KEY (`tbl_user_recebeu`)
    REFERENCES `tbl_user` (`usr_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_convites_convites1`
    FOREIGN KEY (`convites_id`)
    REFERENCES `convites` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB

The accessories were generated by Workbench