Count number of rows before and after a particular row in BigQuery?

I have a table containing date and events. There is event named 'A'. I want to find out how many events occurred before and after event 'A' in Sql Bigquery.\nfor Example,

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

The answer would be something like this.

  User       Event    Before   After\n  123          A       2        2\n

I have tried many queries but its not working. Any Idea, how to solve this problem?

below is for BigQuery Standard SQL

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

Ryan-Dallas

posted on