Count number of events before and after a particular event in SQL?

Question

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. for Example,

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

The answer would be something like this.

  User       Event    Before   After
  123          A       2        2

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

sure :o) - is that example just oversimplified or you really have just one event A in that table? I think in your original question I saw two rows with A — Mikhail Berlyant, Feb 13 '18 at 18:21
Actually, Its from a table where I have multiple users. Each user has Event A only once, but other events are repeated multiple times. I separated the data for a single user. This way I thought if the issue is solved, I can implement it for the complete table. The actual answer would be : userid event(i.e A) before value after value. — VSR, Feb 13 '18 at 18:25

score 2 · Accepted Answer · answered Feb 13 '18 at 18:36

2

below is for BigQuery Standard 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'

answered Feb 13 '18 at 18:36

Mikhail Berlyant

165,386
8
154
230

In this answer, I would have to manually union all the possible events. That would difficult to implement for large table. – VSR Feb 13 '18 at 18:53
what do you mean? is this something you have in your question? :o) – Mikhail Berlyant Feb 13 '18 at 18:57
just remove `WHERE event = 'A'` and you get before and after for each and every event for all users! try and let m eknow – Mikhail Berlyant Feb 13 '18 at 19:03
Hey Mikhail, If I have to change query to check how many times a particular event took place before A. But the issue is that the event that I have to check contains a particular prefix. Means I have to check events that start with ( X.Y.then some event name). So, X.Y.SomeEvent are the events that I have to set counter for. Any idea? – VSR Feb 14 '18 at 18:12
I think, you should post this as a new question so we are not limited by format of comments – Mikhail Berlyant Feb 14 '18 at 18:34

score 0 · Answer 2 · answered Feb 13 '18 at 16:00

For each "A", you can get the number of events to the next "A" using row_number() and lead():

select t.*,
       (lead(seqnum) over (order by date) - seqnum - 1) as num_other_events
from (select t.*, row_number() over (order by date) as seqnum
      from t
     ) t
where event = 'A';

This produces the results for each "A". Given that you have three "A"s in your sample data and only want "2", I'm not sure what logic is used for that.

score 0 · Answer 3 · edited Jan 08 '19 at 12:30

0

Hope this answers your question

Create table #temp(T_date varchar(100),Events varchar(100))

insert into #temp values
('2018-02-13','A'),
('2018-02-12','B'),
('2018-02-10','C'),
('2018-02-11','A'),
('2018-02-01','X'),
('2018-02-06','A')

select max(rn)-min(rn)
from
(
select *,ROW_NUMBER() over(order by (select 1)) as rn from #temp
)a
where Events='A'

edited Jan 08 '19 at 12:30

John Sardinha

3,566
6
25
55

answered Feb 13 '18 at 16:12

Suraj Choudhary

37
5

score 0 · Answer 4 · answered Feb 13 '18 at 18:14

If you want to count number of events as they appear in the table before the row with event A, there is no way to do this because BigQuery doesn't preserve physical order of rows in a table.

If you want to count Before and After using the date column, you can do

WITH
  events AS (
  SELECT
    DATE('2018-02-13') AS event_date,
    "D" AS event
  UNION ALL
  SELECT
    DATE('2018-02-12') AS event_date,
    "B" AS event
  UNION ALL
  SELECT
    DATE('2018-02-10') AS event_date,
    "C" AS event
  UNION ALL
  SELECT
    DATE('2018-02-11') AS event_date,
    "A" AS event
  UNION ALL
  SELECT
    DATE('2018-02-01') AS event_date,
    "X" AS event),
  event_a AS (
  SELECT
    *
  FROM
    events
  WHERE
    event = "A")
SELECT
  ANY_VALUE(event_a.event) AS Event,
  COUNTIF(events.event_date<event_a.event_date) AS Before,
  COUNTIF(events.event_date>event_a.event_date) AS After
FROM
  events,
  event_a

Count number of events before and after a particular event in SQL?

4 Answers4

Linked