test

SELECT *
-- исправил на LEAD, т.к. необходимо посчитать процент следующего месяца по сравнению с предыдущим
,ROUND((LEAD(tips_sum)  OVER (PARTITION BY taxi_id ORDER BY  year_month ) - tips_sum)/tips_sum*100., 2)  AS tips_change
-- изменил расчет процента
from(
SELECT

  taxi_id,
  CAST(TIMESTAMP_TRUNC(trip_start_timestamp, MONTH) AS DATE) AS year_month,
  sum(tips) AS tips_sum,

FROM
  bigquery-public-data.chicago_taxi_trips.taxi_trips
WHERE CAST(TIMESTAMP_TRUNC(trip_start_timestamp, MONTH) AS DATE) > '2022-04-01'
    AND taxi_id IN (
                SELECT taxi_id FROM (
                    SELECT
                      taxi_id,
                      CAST(TIMESTAMP_TRUNC(trip_start_timestamp, MONTH) AS DATE) AS month,
                      sum(tips) AS count_tips
                    FROM
                      bigquery-public-data.chicago_taxi_trips.taxi_trips
                    WHERE CAST(TIMESTAMP_TRUNC(trip_start_timestamp, MONTH) AS DATE) = '2022-04-01'
                    group by 1, 2
                    ORDER BY 3 DESC -- вставил сортировку, совсем про нее забыл

                    LIMIT 3))
GROUP BY 1, 2
ORDER BY 1, 2
)