ALTER VIEW authors_view (id, user_id, first_name, gender, birth_date, birth_country_id, city, edu_level_id, specialization_id, price_per_hour_id, cv, payment_method, user_status, author_status, test_id, avg_test, ranking) 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_per_hour_id,
    t1.cv,
    t1.payment_method,
    t2.status                                                                           as 'user_status',
    t1.author_status_id                                                                 as 'author_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',
    TRUNCATE(((SELECT avg_test) + IFNULL(score.average_article,0)) / (IFNULL(score.num_articles,0) + 1), 2) as 'ranking'
  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
    LEFT JOIN author_score as score ON score.author_id = t1.id
  WHERE t2.registration_step = 10
  ;