User:Elitre (WMF)/HistoryToList

This is a query which retrieves active users on a wiki page, and turns their usernames into a list of their talk pages which can be targeted by a MassMessage.

It's useful, for example, to contact all the users who might have participated in a consultation, or who used to be active on a feedback page, but don't watch the related page anymore.

Please credit it.wp User:Incola for it.

Queries

All people who commented on the VE feedback page at it.wp excluding bots;

USE itwiki_p;
SELECT CONCAT('*{{target | page = Discussioni utente:', rev_user_text, ' | site = it.wikipedia.org}}')
FROM page, revision
WHERE page_id = rev_page
AND page_namespace = 4
AND page_title = 'VisualEditor/Commenti'
AND rev_user_text IN
(SELECT user_name
FROM user
WHERE user_id NOT IN
(SELECT ug_user
FROM user_groups
WHERE ug_group = 'bot'))
GROUP BY rev_user_text
HAVING COUNT(*) >= 3
ORDER BY COUNT(*) DESC

All people who commented on the VE feedback page at it.wp after January 2015 excluding bots;

USE itwiki_p;
SELECT CONCAT('*{{target | page = Discussioni utente:', rev_user_text, ' | site = it.wikipedia.org}}')
FROM page, revision
WHERE page_id = rev_page
AND page_namespace = 4
AND page_title = 'VisualEditor/Commenti'
AND rev_user_text IN
(SELECT user_name
FROM user
WHERE user_id NOT IN
(SELECT ug_user
FROM user_groups
WHERE ug_group = 'bot')
AND user_id IN
(SELECT rev_user
FROM revision
WHERE rev_timestamp > 20150101000000))
GROUP BY rev_user_text
HAVING COUNT(*) >= 3
ORDER BY COUNT(*) DESC

All people who commented on the VE feedback page at it.wp in the past 6 months excluding bots and banned/blocked editors;

USE itwiki_p;
SELECT CONCAT('*{{target | page = Discussioni utente:', rev_user_text, ' | site = it.wikipedia.org}}')
FROM page, revision
WHERE page_id = rev_page
AND page_namespace = 4
AND page_title = 'VisualEditor/Commenti'
AND rev_user_text IN
(SELECT user_name
FROM user
WHERE user_id NOT IN
(SELECT ug_user
FROM user_groups
WHERE ug_group = 'bot')
AND user_id IN
(SELECT rev_user
FROM revision, page
WHERE page_id = rev_page
AND page_namespace = 4
AND page_title = 'VisualEditor/Commenti'
AND rev_timestamp > NOW() - INTERVAL 6 MONTH)
AND user_id NOT IN
(SELECT ipb_user
FROM ipblocks
WHERE (ipb_expiry = 'infinity' OR ipb_expiry > NOW())
AND ipb_user <> 0))
GROUP BY rev_user_text
HAVING COUNT(*) >= 3
ORDER BY COUNT(*) DESC

Result looks something like this:

*{{target | page = Discussioni utente:Elitre (WMF) | site = it.wikipedia.org}}
*{{target | page = Discussioni utente:Lepido | site = it.wikipedia.org}}
*{{target | page = Discussioni utente:Nnvu | site = it.wikipedia.org}}
*{{target | page = Discussioni utente:Baruneju | site = it.wikipedia.org}}
etc.etc.