Advertisement
shapoval

Verified / Non-verified emails statistic

Jan 9th, 2019
191
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 1.09 KB | None | 0 0
  1. SELECT `users`.`date`,
  2.        `verified`.`count` AS `verified emails`,
  3.        `non_verified`.`count` AS `non verified emails`,
  4.        `users`.`count` AS `total registrations`,
  5.        CONCAT(ROUND(( `verified`.`count` / `users`.`count` * 100 ), 2), '%') AS `verified percentage`,
  6.        CONCAT(ROUND(( `non_verified`.`count` / `users`.`count` * 100 ), 2), '%') AS `non verified percentage`
  7. FROM (
  8.     SELECT DATE_FORMAT(`registration_date`, '%Y - %m') AS `date`, COUNT(*) AS `count`
  9.     FROM `tbl_user`
  10.     GROUP BY DATE_FORMAT(`registration_date`, '%Y%m')
  11. ) AS `users`
  12. LEFT JOIN (
  13.     SELECT DATE_FORMAT(`registration_date`, '%Y - %m') AS `date`, COUNT(*) AS `count`
  14.     FROM `tbl_user`
  15.     WHERE `email_verified` = 0
  16.     GROUP BY DATE_FORMAT(`registration_date`, '%Y%m')
  17. ) AS `non_verified` ON `users`.`date` = `non_verified`.`date`
  18. LEFT JOIN (
  19.     SELECT DATE_FORMAT(`registration_date`, '%Y - %m') AS `date`, COUNT(*) AS `count`
  20.     FROM `tbl_user`
  21.     WHERE `email_verified` = 1
  22.     GROUP BY DATE_FORMAT(`registration_date`, '%Y%m')
  23. ) AS `verified` ON `users`.`date` = `verified`.`date`
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement