优化动态7天队列Firebase BigQuery

我在下面针对我们移动应用程序的数据编写了以下查询。由于用户基数高,我收到400个请求错误 "Resources exceeded during query execution: The query could not be executed in the allotted memory" 当我添加 ORDER BY 在底部。

问:有什么可以优化查询,但仍然保留 订货人 在底部?

我已经添加了firebase的demo数据集,但是我认为他们的数据集太小了,不会有问题(与我的数据集相比,我的数据集有500-1000万条记录)。

SELECT 
  f.user_pseudo_id,
  f.event_timestamp, 
  DATE(TIMESTAMP_MICROS(f.event_timestamp)) as event_timestamp_date,
  f.event_name,
  f.user_first_touch_timestamp,
  DATE(TIMESTAMP_MICROS(f.user_first_touch_timestamp)) as user_first_touch_date,
  CASE WHEN r.has_appRemove >= 1 THEN "removed" ELSE "not-removed" END AS status_after_first7days
FROM `firebase-analytics-sample-data.ios_dataset.app_events_*` f
LEFT JOIN (
    SELECT user_pseudo_id, 1 has_appRemove
    FROM `firebase-analytics-sample-data.ios_dataset.app_events_*`
    WHERE DATE(TIMESTAMP_MICROS(user_first_touch_timestamp)) >= DATE_SUB(CURRENT_DATE(), INTERVAL 10 DAY)
      AND DATE(TIMESTAMP_MICROS(user_first_touch_timestamp)) < DATE_SUB(CURRENT_DATE(), INTERVAL 9 DAY)
      AND _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 10 DAY))
      AND _TABLE_SUFFIX < FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY))
      AND platform = "ANDROID"
      AND event_name = "app_remove"
    GROUP BY user_pseudo_id
    ) r on f.user_pseudo_id = r.user_pseudo_id
WHERE
  DATE(TIMESTAMP_MICROS(user_first_touch_timestamp)) >= DATE_SUB(CURRENT_DATE(), INTERVAL 10 DAY)
  AND DATE(TIMESTAMP_MICROS(user_first_touch_timestamp)) < DATE_SUB(CURRENT_DATE(), INTERVAL 9 DAY)
  AND _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 10 DAY))
  AND _TABLE_SUFFIX < FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY))
  AND platform = "ANDROID" 
ORDER BY 1,2 ASC