DROP VIEW IF EXISTS editors_tests;
CREATE VIEW editors_tests (id, user_id, first_last_name, email, user_status, test_status, avg_test) AS
SELECT  t1.id,
		    t1.user_id,
		    CONCAT(t1.first_name, ' ', t1.last_name) as 'first_last_name',
		    t3.email,
        t3.status as 'user_status',
        t2.status as 'test_status',

        case when t2.status is not null THEN (
          COALESCE(t2.sum_style, 0) +
        	COALESCE(t2.sum_grammar, 0) +
        	COALESCE(t2.sum_briefing, 0) +
        	COALESCE(t2.sum_information, 0) +
        	COALESCE(t2.sum_interest, 0) +
        	COALESCE(t2.sum_tone, 0)
        ) / (
          (t2.sum_style IS NOT NULL) +
        	(t2.sum_grammar IS NOT NULL) +
        	(t2.sum_briefing IS NOT NULL) +
        	(t2.sum_information IS NOT NULL) +
        	(t2.sum_interest IS NOT NULL) +
        	(t2.sum_tone IS NOT NULL)
        ) ELSE null END as 'avg_test'

  FROM 	editors as t1
  LEFT JOIN tests as t2 on t2.checking_user_id = t1.user_id
  JOIN `user`       as t3 on t3.id = t1.user_id
;