SELECT
    t.metric_a,
    t.dim_b,
    DATE_FORMAT(t.dt_col, '%m-%d-%Y') AS dt_col_fmt,
    t.per_day
FROM (
    SELECT
        t2.metric_a,
        t2.dim_b,
        t2.dt_col,
        t2.per_day
    FROM (
        SELECT
            SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(payload_col, '"metric_a_key";', -1), ';', 1), ':', -1) AS metric_a,
            SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(payload_col, '"dim_b_key";', -1), ';', 1), ':', -1) AS dim_b,
            dt_col,
            row_id_col,
            @per_day := IF(@current_day = dt_col, @per_day + 1, 1) AS per_day,
            @current_day := dt_col AS current_day
        FROM src_table
        CROSS JOIN (SELECT @per_day := 0) per_day_init
        WHERE dt_col > 'YYYY-MM-DD_START'
          AND dt_col <= 'YYYY-MM-DD_END'
          AND dt_col NOT IN (SELECT dt_col FROM tbl_excluded_dates)
          AND WEEKDAY(dt_col) < 5
          AND client_id_col IN (SELECT a.client_id_col FROM tbl_allowed_clients a)
          AND client_id_col NOT IN (SELECT b.client_id_col FROM tbl_blocked_clients b)
          AND client_id_col IN (CLIENT_ID_PLACEHOLDER)
          AND payload_col LIKE '%"metric_a_key";%'
          AND payload_col NOT LIKE '%"metric_a_key";X;%'
          AND payload_col LIKE '%"dim_b_key";%'
        ORDER BY dt_col, metric_a DESC
    ) t1
    RIGHT JOIN (
        SELECT
            SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(payload_col, '"metric_a_key";', -1), ';', 1), ':', -1) AS metric_a,
            SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(payload_col, '"dim_b_key";', -1), ';', 1), ':', -1) AS dim_b,
            dt_col,
            row_id_col,
            @per_day := IF(@current_day = dt_col, @per_day + 1, 1) AS per_day,
            @current_day := dt_col AS current_day
        FROM src_table
        CROSS JOIN (SELECT @per_day := 0) per_day_init
        WHERE dt_col > 'YYYY-MM-DD_START'
          AND dt_col <= 'YYYY-MM-DD_END'
          AND dt_col NOT IN (SELECT dt_col FROM tbl_excluded_dates)
          AND WEEKDAY(dt_col) < 5
          AND client_id_col IN (SELECT a.client_id_col FROM tbl_allowed_clients a)
          AND client_id_col NOT IN (SELECT b.client_id_col FROM tbl_blocked_clients b)
          AND client_id_col IN (CLIENT_ID_PLACEHOLDER)
          AND payload_col LIKE '%"metric_a_key";%'
          AND payload_col NOT LIKE '%"metric_a_key";X;%'
          AND payload_col LIKE '%"dim_b_key";%'
        ORDER BY dt_col, metric_a DESC
    ) t2
      ON t1.row_id_col = t2.row_id_col
) t
WHERE t.per_day <= 5;



Refactored 


SELECT
    t.metric_a,
    t.dim_b,
    DATE_FORMAT(t.dt_col, '%m-%d-%Y') AS dt_col_fmt,
    t.per_day
FROM (
    SELECT
        t2.metric_a,
        t2.dim_b,
        t2.dt_col,
        t2.per_day
    FROM (
        SELECT
            SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(payload_col, '"metric_a_key";', -1), ';', 1), ':', -1) AS metric_a,
            SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(payload_col, '"dim_b_key";', -1), ';', 1), ':', -1) AS dim_b,
            dt_col,
            row_id_col,
            @per_day := IF(@current_day = dt_col, @per_day + 1, 1) AS per_day,
            @current_day := dt_col AS current_day
        FROM src_table
        CROSS JOIN (SELECT @per_day := 0) per_day_init
        WHERE dt_col > 'YYYY-MM-DD_START'
          AND dt_col <= 'YYYY-MM-DD_END'
          AND dt_col NOT IN (SELECT dt_col FROM tbl_excluded_dates)
          AND WEEKDAY(dt_col) < 5
          AND client_id_col IN (SELECT a.client_id_col FROM tbl_allowed_clients a)
          AND client_id_col NOT IN (SELECT b.client_id_col FROM tbl_blocked_clients b)
          AND client_id_col IN (CLIENT_ID_PLACEHOLDER)
          AND payload_col LIKE '%"metric_a_key";%'
          AND payload_col NOT LIKE '%"metric_a_key";X;%'
          AND payload_col LIKE '%"dim_b_key";%'
        ORDER BY dt_col, metric_a DESC
    ) t1
    RIGHT JOIN (
        SELECT
            SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(payload_col, '"metric_a_key";', -1), ';', 1), ':', -1) AS metric_a,
            SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(payload_col, '"dim_b_key";', -1), ';', 1), ':', -1) AS dim_b,
            dt_col,
            row_id_col,
            @per_day := IF(@current_day = dt_col, @per_day + 1, 1) AS per_day,
            @current_day := dt_col AS current_day
        FROM src_table
        CROSS JOIN (SELECT @per_day := 0) per_day_init
        WHERE dt_col > 'YYYY-MM-DD_START'
          AND dt_col <= 'YYYY-MM-DD_END'
          AND dt_col NOT IN (SELECT dt_col FROM tbl_excluded_dates)
          AND WEEKDAY(dt_col) < 5
          AND client_id_col IN (SELECT a.client_id_col FROM tbl_allowed_clients a)
          AND client_id_col NOT IN (SELECT b.client_id_col FROM tbl_blocked_clients b)
          AND client_id_col IN (CLIENT_ID_PLACEHOLDER)
          AND payload_col LIKE '%"metric_a_key";%'
          AND payload_col NOT LIKE '%"metric_a_key";X;%'
          AND payload_col LIKE '%"dim_b_key";%'
        ORDER BY dt_col, metric_a DESC
    ) t2
      ON t1.row_id_col = t2.row_id_col
) t
WHERE t.per_day <= 5;