我想出来了。做了些小小的改变，效果不错：

#### OPEN STORED PROCEDURE

CREATE PROCEDURE LadderConversionUpDown9()
    BEGIN 

### set parameter at top of stored procedure - alissa will also look it up; sam will look it up for 12/12

#### DROP TABLE IF EXISTS IF NEEDED

    DROP TABLE IF EXISTS cte_leenk_ladder_history;
    DROP TABLE IF EXISTS cte_ladder_value_between; 
    DROP TABLE IF EXISTS cte_current_record_date; 
    DROP TABLE IF EXISTS cte_current_record_ladder_level;
    DROP TABLE IF EXISTS cte_ladder_value_before_current;
    DROP TABLE IF EXISTS cte_ladder_date_before_current_period; 
    DROP TABLE IF EXISTS cte_ladder_level_before_current_period; 
    DROP TABLE IF EXISTS cte_additions_to_current_rung;
    DROP TABLE IF EXISTS cte_start_of_period_count; 
    DROP TABLE IF EXISTS cte_sum_additions_to_ladder; 

#### CREATE PARAMETERS FOR LADDERS

SET @ladder_type = 'ladder_advocacy'; # remove in data studio 

    CREATE TEMPORARY TABLE cte_leenk_ladder_history
        SELECT 
        member_id
        ,ladder_change
        ,date_trigger_event
        ,@ladder_type
        ,CASE WHEN @ladder_type='ladder_advocacy' THEN ladder_advocacy
                WHEN @ladder_type='ladder_elected' THEN ladder_elected
                WHEN @ladder_type='ladder_policy' THEN ladder_policy
                WHEN @ladder_type='ladder_organizing' THEN ladder_organizing
                WHEN @ladder_type='ladder_collective' THEN ladder_collective
                WHEN @ladder_type='ladder_engagement' THEN ladder_engagement
            END
            AS ladder_value
        FROM leeds_new.leenk_ladder_history;

#### GET CURRENT LADDER INFO

# 1. Get member_id AND ladder hist value between X dates ## NEED TO FIX DATES IN DATA STUDIO

    CREATE TEMPORARY TABLE cte_ladder_value_between
        SELECT
        cte_leenk_ladder_history.member_id AS member_id,
        cte_leenk_ladder_history.date_trigger_event

        FROM cte_leenk_ladder_history
            WHERE cte_leenk_ladder_history.ladder_change = 1 AND cte_leenk_ladder_history.ladder_value IS NOT NULL
            AND date_trigger_event BETWEEN CAST('2018-01-01' AS DATE) AND CAST('2018-06-01' AS DATE);

# 2. First get members CURRENT record date

    CREATE TEMPORARY TABLE cte_current_record_date
        SELECT
        cte_ladder_value_between.member_id AS member_id,
        MAX(cte_ladder_value_between.date_trigger_event) AS date_trigger_event_max

        FROM cte_ladder_value_between
        GROUP BY cte_ladder_value_between.member_id;

# 3. Get the rung value for the CURRENT ladder level

    CREATE TEMPORARY TABLE cte_current_record_ladder_level
        SELECT
        cte_leenk_ladder_history.member_id,
        cte_leenk_ladder_history.ladder_value,
        cte_leenk_ladder_history.date_trigger_event

        FROM cte_leenk_ladder_history
            INNER JOIN
                cte_current_record_date ON cte_leenk_ladder_history.member_id = cte_current_record_date.member_id
                AND cte_leenk_ladder_history.date_trigger_event = cte_current_record_date.date_trigger_event_max
        WHERE cte_leenk_ladder_history.ladder_change = 1 AND cte_leenk_ladder_history.ladder_value IS NOT NULL;

#### GET LADDER IMMEDIATELY BEFORE CURRENT PERIOD

# 4. Get member_id AND ladder hist value between X dates BEFORE CURRENT PERIOD

    CREATE TEMPORARY TABLE cte_ladder_value_before_current
        SELECT
        cte_leenk_ladder_history.member_id AS member_id,
        cte_leenk_ladder_history.date_trigger_event

        FROM cte_leenk_ladder_history
            WHERE cte_leenk_ladder_history.ladder_change = 1 AND cte_leenk_ladder_history.ladder_value IS NOT NULL
            AND date_trigger_event < CAST('2018-01-01' AS DATE);

# 5. First get members most recent record date BEFORE current period

    CREATE TEMPORARY TABLE cte_ladder_date_before_current_period
        SELECT
        cte_ladder_value_before_current.member_id AS member_id,
        MAX(cte_ladder_value_before_current.date_trigger_event) AS date_trigger_event_max

        FROM cte_ladder_value_before_current
        GROUP BY cte_ladder_value_before_current.member_id;

# 6. Get the rung value for the most recent ladder level BEFORE

    CREATE TEMPORARY TABLE cte_ladder_level_before_current_period
        SELECT
        cte_leenk_ladder_history.member_id,
        cte_leenk_ladder_history.ladder_value,
        cte_leenk_ladder_history.date_trigger_event

        FROM cte_leenk_ladder_history
            INNER JOIN
                cte_ladder_date_before_current_period ON cte_leenk_ladder_history.member_id = cte_ladder_date_before_current_period.member_id
                AND cte_leenk_ladder_history.date_trigger_event = cte_ladder_date_before_current_period.date_trigger_event_max
        WHERE cte_leenk_ladder_history.ladder_change = 1 AND cte_leenk_ladder_history.ladder_value IS NOT NULL;

#### COUNT CURRENT & START OF PERIOD LADDER LEVELS, THEN DIFFERENCE

# 7. addition counts

    CREATE TEMPORARY TABLE cte_additions_to_current_rung
        SELECT
            cte_current_record_ladder_level.ladder_value,
            COUNT(cte_current_record_ladder_level.ladder_value) AS additions_to_current_rung

        FROM cte_current_record_ladder_level
        GROUP BY cte_current_record_ladder_level.ladder_value;

# 8. Start of Period COUNTs

    CREATE TEMPORARY TABLE cte_start_of_period_count
        SELECT
            cte_ladder_level_before_current_period.ladder_value,
            COUNT(cte_ladder_level_before_current_period.ladder_value) AS start_of_period_count

        FROM cte_ladder_level_before_current_period
        GROUP BY cte_ladder_level_before_current_period.ladder_value;

# 9. SUM additions to rung + start of period additions

        SELECT
            cte_additions_to_current_rung.ladder_value,
            additions_to_current_rung,
            start_of_period_count,
            additions_to_current_rung + start_of_period_count AS total_ladder_counts
        FROM cte_additions_to_current_rung
            INNER JOIN cte_start_of_period_count ON cte_additions_to_current_rung.ladder_value = cte_start_of_period_count.ladder_value;

# 10. LOST from rung

# count IDs where ladder_current <> ladder_beginning -- will talk about 12/12

   # SELECT
    #    cte_current_record_ladder_level.ladder_value as current_ladder_value, 
     #   cte_ladder_level_before_current_period.ladder_value as prior_ladder_value,
      #  count(cte_current_record_ladder_level.ladder_value) as count_current_ladder_value

   # FROM 
    #    cte_current_record_ladder_level
     #       INNER JOIN cte_ladder_level_before_current_period 
      #          ON cte_ladder_level_before_current_period.member_id = cte_ladder_level_before_current_period.member_id
       #     WHERE cte_current_record_ladder_level.ladder_value != cte_ladder_level_before_current_period.ladder_value
        ##   ORDER BY cte_current_record_ladder_level.ladder_value; 

#### END STORED PROCEDURE

    END