From: alchimista <andrepintto@gmail.com>

Date: Mon, 29 Oct 2012 21:42:05

I can do it Helder, can we talk locally and discuss how do you want it, and * when *?

This query request is old. If you no longer require this query to be run, please close this bug as WONTFIX. Thanks!

Ideally, these stats should be updated periodically, but I would like an update before closing this. This is mostly useful to keep an updated table on
https://pt.wikipedia.org/wiki/WP:Gadget#Estat.C3.ADsticas_de_utiliza.C3.A7.C3.A3o
as a workaround for bug 19288.

use ptwiki_p;
SELECT up_property,up_value,COUNT(*) FROM user_properties WHERE up_property LIKE 'gadget%' GROUP BY up_property,up_value;

Resultset (0 rows) on
http://quarry.wmflabs.org/query/3162
and
http://quarry.wmflabs.org/query/219
(due to T60196?)

Here's my query:

SELECT 
  SUBSTR(up_property, 8) as gadget, 
  CAST(up_value AS SIGNED) AS enabled, 
  COUNT(DISTINCT up_user) AS users,
  IFNULL(SUM(recent_edits > 0), 0) AS recently_active_users
FROM user_properties
LEFT JOIN (
  SELECT
    rev_user AS user_id,
    COUNT(*) AS recent_edits
  FROM revision
  WHERE rev_timestamp BETWEEN "20140411" AND "20150411"
  GROUP BY rev_user
) AS recent_activity ON user_id = up_user
WHERE up_property LIKE 'gadget-%'
GROUP BY up_property, up_value;