MariaDB 10.3

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


10.3.28-MariaDB


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),
  INDEX (domain_name)
);
INSERT INTO login (user_id, last_login) 
VALUES 
('pol@blah.ie', '2021-03-06'), 
('xyz@blah.ie', '2021-03-06'), 
('abc@blah.ie', '2021-03-06'), 
('pqr@blah.ie', '2021-03-06'), 
('rst@blah.ie', '2021-03-06'),
('fred@xyz.ie', '2020-03-20'), 
('bill@xyz.ie', '2020-03-15'), 
('mary@xyz.ie', '2020-03-14'), 
('joe@xyz.ie', '2020-02-12'), 
('mike@xyz.ie', '2020-01-31');
SELECT * FROM login;


user_id
last_login
domain_name


abc@blah.ie
2021-03-06 00:00:00
blah.ie

bill@xyz.ie
2020-03-15 00:00:00
xyz.ie

fred@xyz.ie
2020-03-20 00:00:00
xyz.ie

joe@xyz.ie
2020-02-12 00:00:00
xyz.ie

mary@xyz.ie
2020-03-14 00:00:00
xyz.ie

mike@xyz.ie
2020-01-31 00:00:00
xyz.ie

pol@blah.ie
2021-03-06 00:00:00
blah.ie

pqr@blah.ie
2021-03-06 00:00:00
blah.ie

rst@blah.ie
2021-03-06 00:00:00
blah.ie

xyz@blah.ie
2021-03-06 00:00:00
blah.ie


SELECT domain_name, last_login_by_domain 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?


domain_name
last_login_by_domain


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


--
-- The fact that this gives an "answer" doesn't mean that it's a good one!
-- With ONLY_FULL_GROUP_BY not set, you can get answers like this - what does
-- this answer actually mean? 

-- mike@xyz.ie last logged in on the 31/01/2020 - but the MAX value displayed
-- is 20/03/2020 which isn't correct - the perils of not having
-- ONLY_FULL_GROUP_BY set!


SELECT a.user_id
    , substring_index(a.user_id, '@', -1)
    , MAX(a.last_login)
FROM login a
WHERE substring_index(a.user_id, '@', -1) 
IN 
(

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

)
ORDER BY substring_index(a.user_id, '@', -1), a.last_login;


user_id
substring_index(a.user_id, '@', -1)
MAX(a.last_login)


mike@xyz.ie
xyz.ie
2020-03-20 00:00:00

user_id	last_login	domain_name
abc@blah.ie	2021-03-06 00:00:00	blah.ie
bill@xyz.ie	2020-03-15 00:00:00	xyz.ie
fred@xyz.ie	2020-03-20 00:00:00	xyz.ie
joe@xyz.ie	2020-02-12 00:00:00	xyz.ie
mary@xyz.ie	2020-03-14 00:00:00	xyz.ie
mike@xyz.ie	2020-01-31 00:00:00	xyz.ie
pol@blah.ie	2021-03-06 00:00:00	blah.ie
pqr@blah.ie	2021-03-06 00:00:00	blah.ie
rst@blah.ie	2021-03-06 00:00:00	blah.ie
xyz@blah.ie	2021-03-06 00:00:00	blah.ie