USE ptwiki_p;

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;