/* VIEW max_test_translator_id */
CREATE OR REPLACE VIEW max_test_translator_id (user_id, test_id) as
  SELECT
    user_id,
    max(id)
  FROM `tests`
  where `tests`.status = 1
  group by user_id;


/* VIEW translator_score */
  CREATE OR REPLACE VIEW translator_score (translator_id, num_articles, average_article) as
   select
    o.translator_id,
    count(*)       as 'num_articles',
    SUM(IFNULL((select AVG(acs.score)
                from article_comment_score as acs
                where acs.article_last_version = a.offer_participant_delivery_id and acs.score > 0),
               0)) as 'average_article'
  FROM articles AS a
    join offer_participant_deliveries o on a.offer_participant_delivery_id = o.id
    left join offer_partitipants o2 on o.offer_participant_id = o2.id
  where a.article_status_id = 16 and o2.project_status = 1;

