Find Max() tempo atualizado para um grupo de usuários

Veja se é o que você quer

-- this main query retrieve all users under the subquery domain
SELECT a.UserID
    , substring_index(a.UserID, '@', -1)
    , MAX(a.lastupdate)
FROM MyTable a
WHERE substring_index(a.UserID, '@', -1) IN (

    -- this subquery search all domain inactive in last 12 months
    SELECT substring_index(a.UserID, '@', -1) AS domain
    FROM MyTable a
    GROUP BY substring_index(a.UserID, '@', -1)
    HAVING MAX(a.lastupdate) < '2020-03-31'

)
ORDER BY substring_index(a.UserID, '@', -1), a.lastupdate

A melhor maneira de fazer isso é provavelmente a seguinte. Esta solução faz uso da GENERATED COLUMNfuncionalidade do MariaDB >= 10.3 (também no MySQL >= 5.7) - é realmente muito útil para consultas como esta - também torna a consulta muito mais legível.

Todo o código abaixo está disponível no violino aqui :

CREATE TABLE login
(
  user_id VARCHAR (255) NOT NULL PRIMARY KEY,
  last_login TIMESTAMP NOT NULL,
  domain_name VARCHAR (255) 
    GENERATED ALWAYS AS ((SUBSTRING_INDEX(user_id, '@', -1))) VIRTUAL,  -- can be STORED

  INDEX (last_login),  -- indexing up to you if you have lots of data...
  INDEX (domain_name)
);

E então alguns registros de amostra:

INSERT INTO login (user_id, last_login) 
VALUES 
('[email protected]', '2021-03-06'), 
('[email protected]', '2021-03-06'), 
('[email protected]', '2021-03-06'), 
('[email protected]', '2021-03-06'), 
('[email protected]', '2021-03-06'),
('[email protected]', '2020-03-20'), 
('[email protected]', '2020-03-15'), 
('[email protected]', '2020-03-14'), 
('[email protected]', '2020-02-12'), 
('[email protected]', '2020-01-31');

E então execute a consulta:

SELECT * FROM 
(
  SELECT domain_name, MAX(last_login) AS last_login_by_domain
  FROM login
  GROUP BY domain_name
) AS tab
WHERE last_login_by_domain < DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
ORDER BY last_login_by_domain DESC;

-- DESC because you might want to deal with recent ones more urgently?
-- renew subscription... whatever  

Resultado:

domain_name   last_login_by_domain       
     xyz.ie    2020-03-20 00:00:00

Isso funciona em todas as versões do MariaDB >= 10.3 do dbfiddle.uk (um ótimo recurso) e também nas versões do MySQL >= 5.7.

Também funciona quando o ONLY_FULL_GROUP_BY do MySQL está definido! Isso é muito importante - se essa variável não estiver definida, as consultas podem (e vão - também ver aqui e aqui ) retornar resultados errôneos (veja meu comentário para a outra resposta a esta pergunta aqui ).

Por exemplo, basta olhar para a parte inferior do violino, onde apontei o problema que surgiu com a outra resposta neste caso (relativamente simples ) - em uma declaração complexa, esse problema pode levar a todos os tipos de problemas difíceis de encontrar bugs - caveat emptor!

EDIT: Depois de ver os comentários sobre a pergunta ( violino ):

SELECT user_id 
FROM login
WHERE domain_name IN
(
  SELECT domain_name FROM
  (
    SELECT 
      domain_name, MAX(last_login) AS last_login_by_domain
    FROM login
    GROUP BY domain_name
  ) AS tab
  WHERE last_login_by_domain < DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
);

Resultado:

user_id
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]

(A pergunta principal é tratada por subconsultas, como visto em outras respostas. Aqui estão algumas questões secundárias.)

INDEX(UserID, lastupdated)

ajudará o desempenho.

E por que não fazer

HAVING MAX(a.lastupdate) < NOW() - INTERVAL 1 YEAR

Para que você não precise ficar alterando a consulta.