Select same data-set at different date ranges in a single query
Your pseudo-SQL is surprising close to the standard SQL clause FILTER, which has only been implemented by Postgres but unfortunately not by MySQL:

SELECT database_one.users.firstname,
       database_one.users.lastname,
       COUNT(*) AS "Total",
       -- Standard SQL: FILTER
       COUNT(*) FILTER (WHERE database_two.announcements.archived_at &gt; DATE_SUB(NOW(), INTERVAL 1 MONTH)) AS "1 Month"
       COUNT(*) FILTER (WHERE database_two.announcements.archived_at &gt; DATE_SUB(NOW(), INTERVAL 3 MONTH)) AS "3 Months"
       COUNT(*) FILTER (WHERE database_two.announcements.archived_at &gt; DATE_SUB(NOW(), INTERVAL 6 MONTH)) AS "6 Months"
       COUNT(*) FILTER (WHERE database_two.announcements.archived_at &gt; DATE_SUB(NOW(), INTERVAL 1 YEAR)) AS "1 Year"
... 


The good news is that in MySQL you can use equivalent (and standard SQL as well) CASE expressions:

SELECT database_one.users.firstname,
       database_one.users.lastname,
       COUNT(*) AS "Total",
       -- Standard SQL: COUNT(CASE ...)
       COUNT(CASE WHEN database_two.announcements.archived_at &gt; DATE_SUB(NOW(), INTERVAL 1 MONTH)
               THEN 1 END) AS "1 Month"
       COUNT(CASE WHEN database_two.announcements.archived_at &gt; DATE_SUB(NOW(), INTERVAL 3 MONTH)
               THEN 1 END) AS "3 Months"
       COUNT(CASE WHEN database_two.announcements.archived_at &gt; DATE_SUB(NOW(), INTERVAL 6 MONTH)
               THEN 1 END) AS "6 Months"
       COUNT(CASE WHEN database_two.announcements.archived_at &gt; DATE_SUB(NOW(), INTERVAL 1 YEAR)
               THEN 1 END) AS "1 Year"
... 


or the more compact but non-standard SUM() that uses the implicit conversions of boolean values (TRUE and FALSE) to 1 and 0:

SELECT database_one.users.firstname,
       database_one.users.lastname,
       COUNT(*) AS "Total",
       -- Non-standard SQL: SUM(boolean expression)
       SUM(database_two.announcements.archived_at &gt; DATE_SUB(NOW(), INTERVAL 1 MONTH)
          ) AS "1 Month"
       SUM(database_two.announcements.archived_at &gt; DATE_SUB(NOW(), INTERVAL 3 MONTH)
          ) AS "3 Months"
       SUM(database_two.announcements.archived_at &gt; DATE_SUB(NOW(), INTERVAL 6 MONTH)
          ) AS "6 Months"
       SUM(database_two.announcements.archived_at &gt; DATE_SUB(NOW(), INTERVAL 1 YEAR)
          ) AS "1 Year"
... 




By the way, you could use aliases for the various tables in the FROM clauses so the query becomes shorter and usually more readable.

Also the announcements.id is ambiguous in the SELECT orr ORDER BY clause after a GROUP BY, because it is not in the group by list. Use MIN() or MAX() or some other ORDER BY instead.

The query with aliases - and assuming you choose the SUM() solution from above:

SELECT u.firstname,
       u.lastname,
       COUNT(*) AS "Total",
       -- Non-standard SQL: SUM(boolean expression)
       SUM(a.archived_at &gt; DATE_SUB(NOW(), INTERVAL 1 MONTH)) AS "1 Month"
       SUM(a.archived_at &gt; DATE_SUB(NOW(), INTERVAL 3 MONTH)) AS "3 Months"
       SUM(a.archived_at &gt; DATE_SUB(NOW(), INTERVAL 6 MONTH)) AS "6 Months"
       SUM(a.archived_at &gt; DATE_SUB(NOW(), INTERVAL 1 YEAR))  AS "1 Year"
FROM database_two.announcements  AS a
JOIN database_one.prospect_evaluations  AS pe
    ON pe.announcement_id = a.id
JOIN database_one.users  AS u
    ON pe.user_id = u.id
WHERE a.deleted_at IS NULL
  AND a.archived_at IS NOT NULL
  AND a.crmstate &lt;&gt; 'Scartata'
GROUP BY pe.user_id
ORDER BY MAX(a.id) DESC ;