Statistics on OpenOffice.org CEE tigris database

Email addresses using multiple accounts

Number of email addresses that have several accounts and completed the registration processs by using

SELECT COUNT(EMAIL),EMAIL FROM HELM_USER WHERE TIMES_LOGGED_IN > 0 GROUP BY EMAIL HAVING COUNT(EMAIL) > 1

returned 8717 results.

Restricting that further by counting only accounts which have last logged in during the latest 18 month by using

SELECT COUNT(EMAIL), EMAIL FROM HELM_USER WHERE TIMES_LOGGED_IN > 0 AND LAST_LOGGED_IN >= DATE_ADD(NOW(), INTERVAL -18 month) GROUP BY EMAIL HAVING COUNT(EMAIL) > 1

returned 2590 results.

number of accounts, accounts used last 18 month, etc ...

SELECT COUNT(LOGIN_NAME) FROM HELM_USER WHERE TIMES_LOGGED_IN > 0

511656

SELECT COUNT(DISTINCT EMAIL) FROM HELM_USER WHERE TIMES_LOGGED_IN > 0

502237

never logged in ( = registration not completed ) :

SELECT COUNT(LOGIN_NAME) FROM HELM_USER WHERE TIMES_LOGGED_IN=0

335658

only logged in once ( registration completed but not used again ) :

SELECT COUNT(LOGIN_NAME) FROM HELM_USER WHERE TIMES_LOGGED_IN=1

253669

last logged in in the last 18 month:

SELECT COUNT(LOGIN_NAME) FROM HELM_USER WHERE TIMES_LOGGED_IN >0 AND LAST_LOGGED_IN >= DATE_ADD(NOW(), INTERVAL -18 month)

217458

last logged in in the last 12 month:

SELECT COUNT(LOGIN_NAME) FROM HELM_USER WHERE TIMES_LOGGED_IN >0 AND LAST_LOGGED_IN >= DATE_ADD(NOW(), INTERVAL -12 month)

166711

last logged in before the last 18 month:

SELECT COUNT(LOGIN_NAME) FROM HELM_USER WHERE TIMES_LOGGED_IN >0 AND LAST_LOGGED_IN < DATE_ADD(NOW(), INTERVAL -18 month)

294200

last logged in before the last 12 month:

SELECT COUNT(LOGIN_NAME) FROM HELM_USER WHERE TIMES_LOGGED_IN >0 AND LAST_LOGGED_IN < DATE_ADD(NOW(), INTERVAL -12 month)

344948

last logged in in the last 18 month starting with a digit:

SELECT COUNT(LOGIN_NAME) FROM HELM_USER WHERE TIMES_LOGGED_IN >= 0 AND LAST_LOGGED_IN >= DATE_ADD(NOW(), INTERVAL -18 month) AND (SUBSTRING(LOGIN_NAME,1,1) IN ('0','1','2','3','4','5','6','7','8','9'))

3279

last logged in before the last 18 month only digits:

SELECT COUNT(LOGIN_NAME) FROM HELM_USER WHERE TIMES_LOGGED_IN >0 AND LAST_LOGGED_IN >= DATE_ADD(NOW(), INTERVAL -18 month) AND LOGIN_NAME REGEXP '^[0-9]+$'

487