How to count (non) consecutive records by day in bigquery?

Question

Each malfunction of a device is logged. Each entry contains a customer_id, device_id and the timestamp:

+-------------+-----------+-----------------------+
| customer_id | device_id |  timestamp            |
+-------------+-----------+-----------------------+
| 1           | 1         |  2019-02-12T01:00:00  |
| 2           | 2         |  2019-02-12T01:00:00  |
| 1           | 1         |  2019-02-12T02:00:00  |
| 1           | 1         |  2019-02-12T03:00:00  |
+-------------+-----------+-----------------------+

Malfunction logs are collected every hour. I am interested in following information:

total number of malfunctions per customer per day
number of consecutive malfunctions per customer per day
number of not consecutive malfunctions per customer per day

A device may have a malfunction for multiple hours, which might indicate a hardware failure. On the other side, if a device has a malfunction that does not span multiple hours, it might just be a wrong usage of the device.

The result should look like this:

+-------------+-----------+---------------------+-----------------+------------+-----------------------+
| customer_id | device_id | total | consecutive | non consecutive |  day       | last_recording        |
+-----+-------------------+-------+-------------+-----------------+------------------------------------+
| 1           | 1         | 3     |  1          | 2               | 2019-02-12 |  2019-02-12T03:00:00  |
| 2           | 2         | 1     |  0          | 1               | 2019-02-12 |  2019-02-12T01:00:00  |
+-------------+-----------+-------+-------------+-----------------+------------+-----------------------+

In the example above device 1 reported a malfunction at 2019-02-12T02:00:00 which is considered "non consecutive" and just after that another one at 2019-02-12T03:00:00, which is considered "consecutive".

I want to create a query, that generates such result. What I've tried

SELECT customer_id, device_id, COUNT(customer_id) AS count, FORMAT_TIMESTAMP("%Y-%m-%d", TIMESTAMP(timestamp)) as day
FROM `malfunctions`
GROUP BY day, customer_id, device_id

This way I can get the number of total malfunctions by customer by day. I think I have to use the LEAD operator to get the (non) consecutive count, but I am not sure how. Any ideas? The results should be "rolling" by day.

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2019-03-26T15:55:41

Below is for BigQuery Standard SQL

#standardSQL
SELECT customer_id, device_id, day, SUM(batch_count) total, 
  SUM(batch_count) - COUNTIF(batch_count = 1) consecutive,
  COUNTIF(batch_count = 1) non_consecutive, 
  ARRAY_AGG(STRUCT(batch AS batch, batch_count AS batch_count, first_recording AS first_recording, last_recording AS last_recording)) details
FROM (
  SELECT customer_id, device_id, day, batch, 
    COUNT(1) batch_count,
    MIN(ts) first_recording,
    MAX(ts) last_recording
  FROM (
    SELECT customer_id, device_id, ts, day,
      COUNTIF(gap) OVER(PARTITION BY customer_id, device_id, day ORDER BY  ts) batch
    FROM (
      SELECT customer_id, device_id, ts, DATE(ts) day,
        IFNULL(TIMESTAMP_DIFF(ts, LAG(ts) OVER(PARTITION BY customer_id, device_id, DATE(ts) ORDER BY  ts), HOUR), 777) > 1 gap
      FROM `project.dataset.malfunctions`
    )
  )
  GROUP BY customer_id, device_id, day, batch
)
GROUP BY customer_id, device_id, day

You can test, play with above using dummy data as in below example

#standardSQL
WITH `project.dataset.malfunctions` AS (
  SELECT 1 customer_id, 1 device_id, TIMESTAMP '2019-02-12T01:00:00' ts UNION ALL
  SELECT 1, 1, '2019-02-12T02:00:00' UNION ALL
  SELECT 1, 1, '2019-02-12T03:00:00' UNION ALL
  SELECT 1, 1, '2019-02-12T04:00:00' UNION ALL
  SELECT 1, 1, '2019-02-12T09:00:00' UNION ALL
  SELECT 1, 1, '2019-02-12T10:00:00' UNION ALL
  SELECT 1, 1, '2019-02-13T03:00:00' UNION ALL
  SELECT 2, 2, '2019-02-12T01:00:00' 
)
SELECT customer_id, device_id, day, SUM(batch_count) total, 
  SUM(batch_count) - COUNTIF(batch_count = 1) consecutive,
  COUNTIF(batch_count = 1) non_consecutive, 
  ARRAY_AGG(STRUCT(batch AS batch, batch_count AS batch_count, first_recording AS first_recording, last_recording AS last_recording)) details
FROM (
  SELECT customer_id, device_id, day, batch, 
    COUNT(1) batch_count,
    MIN(ts) first_recording,
    MAX(ts) last_recording
  FROM (
    SELECT customer_id, device_id, ts, day,
      COUNTIF(gap) OVER(PARTITION BY customer_id, device_id, day ORDER BY  ts) batch
    FROM (
      SELECT customer_id, device_id, ts, DATE(ts) day,
        IFNULL(TIMESTAMP_DIFF(ts, LAG(ts) OVER(PARTITION BY customer_id, device_id, DATE(ts) ORDER BY  ts), HOUR), 777) > 1 gap
      FROM `project.dataset.malfunctions`
    )
  )
  GROUP BY customer_id, device_id, day, batch
)
GROUP BY customer_id, device_id, day
-- ORDER BY customer_id, device_id, day

with result

How to count (non) consecutive records by day in bigquery?

1 Answers