MySQL query returns different result on second execution

I'm not too familiar with MySQL variables but I'm trying to avoid redundant sub selects while calculating these results. The first execution only returns 1 of the correct calculations. The second execution returns all the correct values.

SELECT
@discount_code := '*******' AS discount_code,
@sales_revenue := (
    SELECT
        CAST(SUM(total_price) AS UNSIGNED)
    FROM
        shopify_orders
    WHERE
        discount_code = @discount_code
) AS sales_revenue,
@rsp_float := (
    SELECT
        revenue_share_percentage / 100
    FROM
        kpi_data
    WHERE
        CODE = @discount_code
) AS rsp_float,
@marketing_spend := (
    SELECT
        fixed_media_cost + @revenue_share
    FROM
        kpi_data
    WHERE
        CODE = @discount_code
) AS marketing_spend,
@roi_dollars := (
    @sales_revenue - @marketing_spend
) AS roi_dollars,
@revenue_share := (@sales_revenue * @rsp_float) AS revenue_share,
@roi := CAST(
    (@roi_dollars /@marketing_spend) AS DECIMAL (5, 2)
) AS roi,
@mroi := CAST(
    (
        @sales_revenue / @marketing_spend
    ) AS DECIMAL (5, 2)
) AS mroi;

First execution:

Second execution:

The order of columns is important, if you are going to use mysql variables this way. You are using @revenue_share before it is calculated. So, the first time the query runs, NULL is used for the calculation of @marketing_spend. The second time the query runs, the variable has a value saved from the 1st run.

Additionally, the two subqueries that have FROM kpi_data can be combined in one. And you don't really need to use variables. You can get the same results using derived tables and one parameter only (@discount_code), used three times:

SELECT
    @discount_code AS discount_code,
    sales_revenue,
    rsp_float,
    marketing_spend
    sales_revenue - marketing_spend AS roi_dollars,
    revenue_share,
    CAST( ((sales_revenue - marketing_spend) / marketing_spend) AS DECIMAL (5, 2) )
        AS roi,
    CAST( (sales_revenue / marketing_spend) AS DECIMAL (5, 2) )
        AS mroi
FROM
    (   SELECT
            sr.sales_revenue,
            kd.revenue_share_percentage / 100  AS rsp_float,
            kd.fixed_media_cost,
            sr.sales_revenue * kd.rsp_float AS revenue_share,
            kd.fixed_media_cost + (sr.sales_revenue * kd.rsp_float) 
                AS marketing_spend 
        FROM
            (   SELECT
                    CAST(SUM(total_price) AS UNSIGNED) AS sales_revenue
                FROM
                    shopify_orders
                WHERE
                    discount_code = @discount_code 
            ) AS sr
          CROSS JOIN
            kpi_data AS kd
        WHERE
            kd.CODE = @discount_code
    ) AS t ;

StackExchange Database Administrators Q#123656, answer score: 6