video-trending-score

SELECT
    views_scores.video_id,
    recency_scores.publish_at, recency_scores.normalized_score as `recency-normalized_score`,
    views_scores.`0-1d` as `views-0-1d`, views_scores.`1-2d` as `views-1-2d`, views_scores.`2-3d` as `views-2-3d`, views_scores.`3-4d` as `views-3-4d`, views_scores.`4-5d` as `views-4-5d`,
    views_scores.`5-6d` as `views-5-6d`, views_scores.`6-7d` as `views-6-7d`, views_scores.`7-8d` as `views-7-8d`, views_scores.`8-9d` as `views-8-9d`, views_scores.`9-10d` as `views-9-10d`,
    views_scores.`total_score` as `views-total_score`, views_scores.normalized_score as `views-normalized_score`,
    comments_scores.`0-1d` as `comments-0-1d`, comments_scores.`1-2d` as `comments-1-2d`, comments_scores.`2-3d` as `comments-2-3d`, comments_scores.`3-4d` as `comments-3-4d`, comments_scores.`4-5d` as `comments-4-5d`,
    comments_scores.`5-6d` as `comments-5-6d`, comments_scores.`6-7d` as `comments-6-7d`, comments_scores.`7-8d` as `comments-7-8d`, comments_scores.`8-9d` as `comments-8-9d`, comments_scores.`9-10d` as `comments-9-10d`,
    comments_scores.`total_score` as `comments-total_score`, comments_scores.normalized_score as `comments-normalized_score`,
    likes_scores.`0-1d` as `likes-0-1d`, likes_scores.`1-2d` as `likes-1-2d`, likes_scores.`2-3d` as `likes-2-3d`, likes_scores.`3-4d` as `likes-3-4d`, likes_scores.`4-5d` as `likes-4-5d`,
    likes_scores.`5-6d` as `likes-5-6d`, likes_scores.`6-7d` as `likes-6-7d`, likes_scores.`7-8d` as `likes-7-8d`, likes_scores.`8-9d` as `likes-8-9d`, likes_scores.`9-10d` as `likes-9-10d`,
    likes_scores.`total_score` as `likes-total_score`, likes_scores.normalized_score as `likes-normalized_score`,
    recency_scores.normalized_score * 4 as `recency-score`, views_scores.normalized_score * 3 as `views-score`, comments_scores.normalized_score * 2 as `comments-score`, likes_scores.normalized_score as `likes-score`,
    IFNULL(recency_scores.normalized_score, 0) * 4 + IFNULL(views_scores.normalized_score, 0) * 3 + IFNULL(comments_scores.normalized_score, 0) * 2 + IFNULL(likes_scores.normalized_score, 0)as `video_trending_score`
FROM (
SELECT tmp2.*, ROUND(tmp2.total_score / 268344 * 100) as `normalized_score`
FROM (
  SELECT
    tmp1.*,
    tmp1.`0-1d` * 10 + tmp1.`1-2d` * 9 + tmp1.`2-3d` * 8 + tmp1.`3-4d` * 7 + tmp1.`4-5d` * 6 +
     tmp1.`5-6d` * 5 + tmp1.`6-7d` * 4 + tmp1.`7-8d` * 3 + tmp1.`8-9d` * 2 + tmp1.`9-10d` * 1 as `total_score`
  FROM (
    SELECT
      vw.video_id,
      SUM(IF(vw.created_at >= NOW() - INTERVAL 1 day, 1, 0)) AS `0-1d`,
      SUM(IF(vw.created_at BETWEEN NOW() - INTERVAL 2 day AND NOW() - INTERVAL 1 day, 1, 0)) AS `1-2d`,
      SUM(IF(vw.created_at BETWEEN NOW() - INTERVAL 3 day AND NOW() - INTERVAL 2 day, 1, 0)) AS `2-3d`,
      SUM(IF(vw.created_at BETWEEN NOW() - INTERVAL 4 day AND NOW() - INTERVAL 3 day, 1, 0)) AS `3-4d`,
      SUM(IF(vw.created_at BETWEEN NOW() - INTERVAL 5 day AND NOW() - INTERVAL 4 day, 1, 0)) AS `4-5d`,
      SUM(IF(vw.created_at BETWEEN NOW() - INTERVAL 6 day AND NOW() - INTERVAL 5 day, 1, 0)) AS `5-6d`,
      SUM(IF(vw.created_at BETWEEN NOW() - INTERVAL 7 day AND NOW() - INTERVAL 6 day, 1, 0)) AS `6-7d`,
      SUM(IF(vw.created_at BETWEEN NOW() - INTERVAL 8 day AND NOW() - INTERVAL 7 day, 1, 0)) AS `7-8d`,
      SUM(IF(vw.created_at BETWEEN NOW() - INTERVAL 9 day AND NOW() - INTERVAL 8 day, 1, 0)) AS `8-9d`,
      SUM(IF(vw.created_at BETWEEN NOW() - INTERVAL 10 day AND NOW() - INTERVAL 9 day, 1, 0)) AS `9-10d`
    FROM `lustery_video_view` as vw
    GROUP BY vw.video_id
  ) as tmp1
) as tmp2
) as views_scores
LEFT JOIN (
  SELECT
    lv.id AS `video_id`,
    lv.publish_at,
    (
      SELECT (11 - COUNT(*)) * 10 FROM `lustery_video` lv_tmp
      WHERE lv_tmp.publish_at <= NOW() AND lv_tmp.publish_at >= lv.publish_at
    ) as normalized_score
  FROM `lustery_video` lv
  WHERE lv.publish_at <= NOW()
  ORDER BY lv.publish_at DESC
  LIMIT 10
) as recency_scores ON views_scores.video_id = recency_scores.video_id
LEFT JOIN (
  SELECT tmp2.*, ROUND(tmp2.total_score / 40 * 100) as `normalized_score`
  FROM (
    SELECT
      tmp1.*,
      tmp1.`0-1d` * 10 + tmp1.`1-2d` * 9 + tmp1.`2-3d` * 8 + tmp1.`3-4d` * 7 + tmp1.`4-5d` * 6 +
       tmp1.`5-6d` * 5 + tmp1.`6-7d` * 4 + tmp1.`7-8d` * 3 + tmp1.`8-9d` * 2 + tmp1.`9-10d` * 1 as `total_score`
    FROM (
      SELECT
        vc.video_id,
        SUM(IF(vc.created_at >= NOW() - INTERVAL 1 day, 1, 0)) AS `0-1d`,
        SUM(IF(vc.created_at BETWEEN NOW() - INTERVAL 2 day AND NOW() - INTERVAL 1 day, 1, 0)) AS `1-2d`,
        SUM(IF(vc.created_at BETWEEN NOW() - INTERVAL 3 day AND NOW() - INTERVAL 2 day, 1, 0)) AS `2-3d`,
        SUM(IF(vc.created_at BETWEEN NOW() - INTERVAL 4 day AND NOW() - INTERVAL 3 day, 1, 0)) AS `3-4d`,
        SUM(IF(vc.created_at BETWEEN NOW() - INTERVAL 5 day AND NOW() - INTERVAL 4 day, 1, 0)) AS `4-5d`,
        SUM(IF(vc.created_at BETWEEN NOW() - INTERVAL 6 day AND NOW() - INTERVAL 5 day, 1, 0)) AS `5-6d`,
        SUM(IF(vc.created_at BETWEEN NOW() - INTERVAL 7 day AND NOW() - INTERVAL 6 day, 1, 0)) AS `6-7d`,
        SUM(IF(vc.created_at BETWEEN NOW() - INTERVAL 8 day AND NOW() - INTERVAL 7 day, 1, 0)) AS `7-8d`,
        SUM(IF(vc.created_at BETWEEN NOW() - INTERVAL 9 day AND NOW() - INTERVAL 8 day, 1, 0)) AS `8-9d`,
        SUM(IF(vc.created_at BETWEEN NOW() - INTERVAL 10 day AND NOW() - INTERVAL 9 day, 1, 0)) AS `9-10d`
      FROM `lustery_video_comment` as vc
      GROUP BY vc.video_id
    ) as tmp1
  ) as tmp2
) as comments_scores ON views_scores.video_id = comments_scores.video_id
LEFT JOIN (
  SELECT tmp2.*, ROUND(tmp2.total_score / 853 * 100) as `normalized_score`
  FROM (
    SELECT
      tmp1.*,
      tmp1.`0-1d` * 10 + tmp1.`1-2d` * 9 + tmp1.`2-3d` * 8 + tmp1.`3-4d` * 7 + tmp1.`4-5d` * 6 +
       tmp1.`5-6d` * 5 + tmp1.`6-7d` * 4 + tmp1.`7-8d` * 3 + tmp1.`8-9d` * 2 + tmp1.`9-10d` * 1 as `total_score`
    FROM (
      SELECT
        vl.video_id,
        SUM(IF(vl.created_at >= NOW() - INTERVAL 1 day, 1, 0)) AS `0-1d`,
        SUM(IF(vl.created_at BETWEEN NOW() - INTERVAL 2 day AND NOW() - INTERVAL 1 day, 1, 0)) AS `1-2d`,
        SUM(IF(vl.created_at BETWEEN NOW() - INTERVAL 3 day AND NOW() - INTERVAL 2 day, 1, 0)) AS `2-3d`,
        SUM(IF(vl.created_at BETWEEN NOW() - INTERVAL 4 day AND NOW() - INTERVAL 3 day, 1, 0)) AS `3-4d`,
        SUM(IF(vl.created_at BETWEEN NOW() - INTERVAL 5 day AND NOW() - INTERVAL 4 day, 1, 0)) AS `4-5d`,
        SUM(IF(vl.created_at BETWEEN NOW() - INTERVAL 6 day AND NOW() - INTERVAL 5 day, 1, 0)) AS `5-6d`,
        SUM(IF(vl.created_at BETWEEN NOW() - INTERVAL 7 day AND NOW() - INTERVAL 6 day, 1, 0)) AS `6-7d`,
        SUM(IF(vl.created_at BETWEEN NOW() - INTERVAL 8 day AND NOW() - INTERVAL 7 day, 1, 0)) AS `7-8d`,
        SUM(IF(vl.created_at BETWEEN NOW() - INTERVAL 9 day AND NOW() - INTERVAL 8 day, 1, 0)) AS `8-9d`,
        SUM(IF(vl.created_at BETWEEN NOW() - INTERVAL 10 day AND NOW() - INTERVAL 9 day, 1, 0)) AS `9-10d`
      FROM `lustery_video_like` as vl
      GROUP BY vl.video_id
    ) as tmp1
  ) as tmp2
) as likes_scores ON views_scores.video_id = likes_scores.video_id
ORDER BY video_trending_score DESC;