CREATE OR REPLACE VIEW  max_test_id (author_id, test_id) as
SELECT author_id, max(id) FROM `tests` where `tests`.status = 1 group by author_id
;

CREATE VIEW authors_view (id, user_id, first_name, gender, birth_date, birth_country_id, city,edu_level_id, specialization_id, price_word, cv, payment_method, user_status, test_id, avg_test) as
SELECT  t1.id,
        t1.user_id,
        CONCAT(t1.first_name, ' ', t1.last_name) as 'first_last_name',
        t1.gender,
        t1.birth_date,
        t1.birth_country_id,
        t1.city,
        t1.edu_level_id,
        t1.specialization_id,
        t1.price_word,
        t1.cv,
        t1.payment_method,
        t2.status as 'user_status',
        t3.id,

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

  FROM 	`authors` as t1


  JOIN `user`     as t2 on t2.id = t1.user_id
  join `tests` as t3 on t3.author_id = t1.id
  					and t3.status = 1
  join max_test_id as t4 on t4.test_id = t3.id
;
