Query SQL

CREATE TABLE `tabla1` (
  `id_cons` INT NOT NULL AUTO_INCREMENT,
  `id_persona` INT NOT NULL,
  `confeccion_doc` VARCHAR(45) NOT NULL,
  `firma1` VARCHAR(45) NOT NULL,
  `contacto` VARCHAR(45) NOT NULL,
  `firma2` VARCHAR(45) NULL,
  `fecha` DATETIME NOT NULL,
  PRIMARY KEY (`id_cons`));

-- insert dummy values
INSERT INTO `tabla1`
(`id_persona`, `confeccion_doc`, `firma1`,`contacto`, `firma2`, `fecha`)
VALUES
(1, "", "", "No contesta", "", "2023-01-01 10:01:04"),
(1, "", "", "No contesta", "", "2023-01-01 12:05:01"),
(1, "", "", "Numero equivocado", "", "2023-01-02 15:00:31"),
(1, "", "", "Se agenda firma en lugar2", "", "2023-01-02 18:01:00"),
(2, "", "", "Numero equivocado", "", "2023-01-03 11:44:06"),
(2, "", "", "Numero equivocado", "", "2023-01-04 09:00:56"),
(2, "", "", "No contesta", "", "2023-01-05 14:05:01"),
(2, "", "", "Se agenda firma en lugar1", "", "2023-01-05 16:10:01"),
(3, "", "", "No contesta", "", "2023-01-03 13:50:11"),
(3, "", "", "Se agenda firma en lugar1", "", "2023-01-03 15:21:45");

-- ranked by date
WITH ranked_contact AS (
  SELECT t1.*, ROW_NUMBER() OVER (PARTITION BY id_persona ORDER BY fecha DESC) AS rn
  FROM `tabla1` AS t1
)

SELECT contacto, count(*) as counter_contact from ranked_contact
where rn = 1 group by contacto;