American Tower

Ante este escenario generé un nuevo flujo en n8n que extrae el listado de las OLT en las que no se reportan potencias de al menos el 25% de las ONTs

WITH ultimas AS (
SELECT
objectname,
MAX("ont-pwr_rx_updated_on") AS ultima_actualizacion
FROM altiplano.potencias
GROUP BY objectname
),
placas AS (
SELECT
substring(
objectname
from '(BA_OLTA_[^-]+_[^-]+-[0-9]+)'
) AS olt_lt,
ultima_actualizacion
FROM ultimas
)
SELECT
olt_lt,
COUNT() AS total_onts,
COUNT() FILTER (
WHERE ultima_actualizacion >= NOW() - INTERVAL '24 hours'
) AS onts_reportando,
COUNT() FILTER (
WHERE ultima_actualizacion < NOW() - INTERVAL '24 hours'
) AS onts_sin_reportar,
ROUND(
100.0 *
COUNT() FILTER (
WHERE ultima_actualizacion >= NOW() - INTERVAL '24 hours'
)
/ COUNT(),
2
) AS porcentaje_reportando
FROM placas
GROUP BY olt_lt
HAVING
100.0 *
COUNT() FILTER (
WHERE ultima_actualizacion >= NOW() - INTERVAL '24 hours'
)
/ COUNT(*) < 75
ORDER BY porcentaje_reportando ASC, total_onts DESC;