MySQL Analyses related to user administration
User-Related
This article offers examples for a user administration related analyses with the dashboard.
Number of created and total users per month
SELECT
CONCAT(DATE_FORMAT(ident.creationTime, '%Y'), '-', DATE_FORMAT(ident.creationTime, '%m')) AS "Month",
SUM(1) AS "Number",
(
SELECT
COUNT(*)
FROM view_identity
WHERE identitytype = 'USER' AND
CONCAT(DATE_FORMAT(creationTime, '%Y'), '-', DATE_FORMAT(ident.creationTime, '%m')) <=
CONCAT(DATE_FORMAT(ident.creationTime, '%Y'), '-', DATE_FORMAT(ident.creationTime, '%m'))
) AS "Total"
FROM view_identity ident WHERE identitytype = 'USER'
GROUP BY CONCAT(DATE_FORMAT(ident.creationTime, '%Y'), '-', DATE_FORMAT(ident.creationTime, '%m'))
ORDER BY CONCAT(DATE_FORMAT(ident.creationTime, '%Y'), '-', DATE_FORMAT(ident.creationTime, '%m'));
© TIM Solutions GmbH | AGB | Datenschutz | Impressum