TryGhost/Ghost#15729

original-query.sql

SELECT
    `posts`.*,
    (WITH `k` AS (
    SELECT
        `member_id`
    FROM
        `members_subscription_created_events`
    WHERE
        posts.id = members_subscription_created_events.attribution_id
UNION
    SELECT
        `member_id`
    FROM
        `members_created_events`
    WHERE
        posts.id = members_created_events.attribution_id)
    SELECT
        count(*)
    FROM
        `k`) AS `count__conversions`,
    `posts`.*,
    (
    SELECT
        count(DISTINCT `members_click_events`.`member_id`)
    FROM
        `members_click_events`
    INNER JOIN `redirects` ON
        `members_click_events`.`redirect_id` = `redirects`.`id`
    WHERE
        posts.id = redirects.post_id) AS `count__clicks`,
    `posts`.*,
    (
    SELECT
        COALESCE(ROUND(AVG(score) * 100), 0)
    FROM
        `members_feedback`
    WHERE
        posts.id = members_feedback.post_id) AS `count__sentiment`,
    `posts`.*,
    (
    SELECT
        count(*)
    FROM
        `members_feedback`
    WHERE
        posts.id = members_feedback.post_id
        AND members_feedback.score = 0) AS `count__negative_feedback`,
    `posts`.*,
    (
    SELECT
        sum(`score`)
    FROM
        `members_feedback`
    WHERE
        posts.id = members_feedback.post_id) AS `count__positive_feedback`
FROM
    `posts`
WHERE
    ((`posts`.`status` IN ('draft', 'scheduled', 'published', 'sent')
        AND `posts`.`id` IN (
        SELECT
            `posts_tags`.`post_id`
        FROM
            `posts_tags`
        INNER JOIN `tags` ON
            `tags`.`id` = `posts_tags`.`tag_id`
        WHERE
            `tags`.`slug` = 'blog'))
    AND `posts`.`TYPE` = 'post')
ORDER BY
    CASE
        WHEN posts.status = 'scheduled' THEN 1
        WHEN posts.status = 'draft' THEN 2
        ELSE 3
    END ASC,
    CASE
        WHEN posts.status != 'draft' THEN posts.published_at
    END DESC,
    posts.updated_at DESC,
    posts.id DESC
LIMIT 30

updated-query.sql

SELECT
    `posts`.*,
    (WITH `k` AS (
    SELECT
        `member_id`, `attribution_id`
    FROM
        `members_subscription_created_events`
UNION
    SELECT
        `member_id`, `attribution_id`
    FROM
        `members_created_events`)
    SELECT
        count(*)
    FROM
        `k` WHERE `k`.attribution_id = `posts`.id) AS `count__conversions`,
    (
    SELECT
        count(DISTINCT `members_click_events`.`member_id`)
    FROM
        `members_click_events`
    INNER JOIN `redirects` ON
        `members_click_events`.`redirect_id` = `redirects`.`id`
    WHERE
        `posts`.`id` = redirects.post_id) AS `count__clicks`,
    (
    SELECT
        COALESCE(ROUND(AVG(score) * 100), 0)
    FROM
        `members_feedback`
    WHERE
        `posts`.`id` = members_feedback.post_id) AS `count__sentiment`,
    (
    SELECT
        count(*)
    FROM
        `members_feedback`
    WHERE
        `posts`.`id` = members_feedback.post_id
        AND members_feedback.score = 0) AS `count__negative_feedback`,
    (
    SELECT
        sum(`score`)
    FROM
        `members_feedback`
    WHERE
        `posts`.`id` = members_feedback.post_id) AS `count__positive_feedback`
FROM
    `posts`
WHERE
    (`posts`.`status` = 'published'
        AND `posts`.`TYPE` = 'post')
ORDER BY
    CASE
        WHEN `posts`.`status` = 'scheduled' THEN 1
        WHEN `posts`.`status` = 'draft' THEN 2
        ELSE 3
    END ASC,
    CASE
        WHEN `posts`.`status` != 'draft' THEN `posts`.`published_at`
    END DESC,
    `posts`.`updated_at` DESC,
    `posts`.`id` DESC
LIMIT 30