计算 SQL 中特定事件之前和之后的事件数量？

我有一个包含日期和事件的表。有一个名为“A”的事件。我想知道 Sql Bigquery 中事件“A”之前和之后发生了多少个事件。 例如，

User           Date             Events
123          2018-02-13            D
123          2018-02-12            B
123          2018-02-10            C
123          2018-02-11            A
123          2018-02-01            X

答案是这样的。

  User       Event    Before   After
  123          A       2        2

我已经尝试了很多查询，但它不起作用。任何想法，如何解决这个问题？

以下适用于 BigQuery 标准 SQL

#standardSQL
WITH `project.dataset.events` AS (
  SELECT 123 user, '2018-02-13' dt, 'D' event UNION ALL
  SELECT 123, '2018-02-12', 'B' UNION ALL
  SELECT 123, '2018-02-11', 'A' UNION ALL
  SELECT 123, '2018-02-10', 'C' UNION ALL
  SELECT 123, '2018-02-01', 'X' 
)
SELECT user, event, before, after 
FROM (
  SELECT user, event, 
    COUNT(1) OVER(PARTITION BY user ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) before,
    COUNT(1) OVER(PARTITION BY user ORDER BY dt ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING ) after
  FROM `project.dataset.events`
)
WHERE event = 'A'