textalytics-queries per use

gistfile1.sql

#Number of active users per service - with a cutoff

SELECT  `service`, COUNT(*) num_users
FROM 
(
SELECT  `service`, `hash_key`, COUNT(*) num_requests
FROM `log`
WHERE `date_operation` > '2014-12-01'
GROUP BY `service`, `hash_key`
ORDER BY num_requests DESC 
) active 
WHERE num_requests  > 100
GROUP BY service
ORDER BY num_users DESC


#active users per service - with a cutoff

SELECT email,  log.`service`, log.`hash_key`, COUNT(*) num_requests
FROM log JOIN license 
ON (log.service, log.hash_key)  = (license.service, license.hash_key)  
WHERE `date_operation` > '2014-12-01'
GROUP BY log.`service`, log.`hash_key`
HAVING num_requests > 100000
ORDER BY num_requests DESC