Bigquery - remove rows based on time difference

user_4283668 0
Viewed 82

I have a single table in BigQuery. It could be any database.

I wanted to remove rows based on a time conditions. What happens is that if users click too quickly it creates a duplicate entry which needs to be removed. But there could be scenarios where two valid leads will come very close and needs to be kept because of different IP addresses or advertiser. We need to dedupe when the rows are within 4 seconds of each other.

I also need to ensure that if a row is flagged as a duplicate, the following rows do not use the duplicate rows timestamp to derive the 4-second flag.


ip_address  datetime                    advertiser  order_number    Comment
34.195.131  2020-07-03 22:45:02.585 UTC homepage    5678            KEEP
34.195.131  2020-07-03 22:45:05.593 UTC homepage    5678            REMOVE - WITHIN 4 SECONDS OF B2
34.195.131  2020-07-03 22:45:08.923 UTC homepage    5678            KEEP - SINCE B3 WAS REMOVED, C4 IS NOW MORE THAN 4 SECONDS FROM B2
34.195.131  2020-07-03 22:45:13.788 UTC homepage    5678            KEEP
34.195.131  2020-07-03 22:45:16.523 UTC homepage    5678            REMOVE - WITHIN 4 SECONDS OF B5
34.195.131  2020-07-03 22:45:20.393 UTC homepage    5678            KEEP - SINCE B6 WAS REMOVED, LESS THAN 4 SECONDS OF B4
34.195.131  2020-07-03 22:45:21.247 UTC homepage    5678            REMOVE - WITHIN 4 SECONDS OF B7
34.195.131  2020-07-03 22:45:24.924 UTC homepage    5678            KEEP - SINCE B8 WAS REMOVED AND MORE THAN 4 SECONDS OF B7
34.195.131  2020-07-03 22:45:27.443 UTC homepage    5678            REMOVE - WITHIN 4 SECONDS OF B9
34.195.131  2020-07-03 22:45:30.561 UTC homepage    5678            KEEP - SINCE B10 WAS REMOVED AND MORE THAN 4 SECONDS OF B9
34.195.131  2020-07-03 22:45:32.561 UTC homepage    5678            REMOVE - WITHIN 4 SECONDS OF B11
34.195.131  2020-07-03 22:45:33.935 UTC homepage    5678            REMOVE - WITHIN 4 SECONDS OF B11
34.195.131  2020-07-03 22:45:36.083 UTC homepage    5678            KEEP - SINCE B12 AND B13 WERE REMOVED AND MORE THAN 4 SECONDS OF B11
34.195.132  2020-07-03 22:45:38.849 UTC homepage    5678            KEEP - EVEN THOUGH WITHIN 4 SECONDS OF B14, THIS IS A DIFFERENT IP_ADDRESS
34.195.132  2020-07-03 22:45:38.949 UTC homepage    1234            KEEP - EVEN THOUGH WITHIN 4 SECONDS OF B15 THIS IS A NEW ORDER_NUMBER

I have tried using CTEs and self join but without any success for now. Could anyone tell me how to do that or pointers how to proceed further?

google-bigquery
1 Answers

I am not very sure about the requirements if you can add description about B2,B3 etc, I guess the comments will be much more clear to decode the required logic. Anyway based on my understanding I implemented following logic:

Create Dummy table:

WITH 
data as

(
SELECT '34.195.131' ip_address,'2020-07-03 22:45:02.585 UTC' datetime,'homepage' advertiser,'5678' order_number
UNION ALL
SELECT '34.195.131' ip_address,'2020-07-03 22:45:05.593 UTC' datetime,'homepage' advertiser,'5678' order_number
UNION ALL
SELECT '34.195.131' ip_address,'2020-07-03 22:45:08.923 UTC' datetime,'homepage' advertiser,'5678' order_number
UNION ALL
SELECT '34.195.131' ip_address,'2020-07-03 22:45:13.788 UTC' datetime,'homepage' advertiser,'5678' order_number
UNION ALL
SELECT '34.195.131' ip_address,'2020-07-03 22:45:16.523 UTC' datetime,'homepage' advertiser,'5678' order_number
UNION ALL
SELECT '34.195.131' ip_address,'2020-07-03 22:45:20.393 UTC' datetime,'homepage' advertiser,'5678' order_number
UNION ALL
SELECT '34.195.131' ip_address,'2020-07-03 22:45:21.247 UTC' datetime,'homepage' advertiser,'5678' order_number
UNION ALL
SELECT '34.195.131' ip_address,'2020-07-03 22:45:24.924 UTC' datetime,'homepage' advertiser,'5678' order_number
UNION ALL
SELECT '34.195.131' ip_address,'2020-07-03 22:45:27.443 UTC' datetime,'homepage' advertiser,'5678' order_number
UNION ALL
SELECT '34.195.131' ip_address,'2020-07-03 22:45:30.561 UTC' datetime,'homepage' advertiser,'5678' order_number
UNION ALL
SELECT '34.195.131' ip_address,'2020-07-03 22:45:32.561 UTC' datetime,'homepage' advertiser,'5678' order_number
UNION ALL
SELECT '34.195.131' ip_address,'2020-07-03 22:45:33.935 UTC' datetime,'homepage' advertiser,'5678' order_number
UNION ALL
SELECT '34.195.131' ip_address,'2020-07-03 22:45:36.083 UTC' datetime,'homepage' advertiser,'5678' order_number
UNION ALL
SELECT '34.195.132' ip_address,'2020-07-03 22:45:38.849 UTC' datetime,'homepage' advertiser,'5678' order_number
UNION ALL
SELECT '34.195.132' ip_address,'2020-07-03 22:45:38.949 UTC' datetime,'homepage' advertiser,'1234' order_number
),

data_corrected
as
(
SELECT ip_address,CAST(datetime As Timestamp) datetime,advertiser,order_number
From data
)

Now the logic, I am using Lag and Lead window functions to grab following and preceding values partitioning records by d.ip_address,advertiser,order_number ordering by datetime and then calculating the time delta.

SELECT d.*, LEAD(datetime)
    OVER (PARTITION BY d.ip_address,advertiser,order_number ORDER BY datetime ASC) AS followed_by_click,
    CASE WHEN TIMESTAMP_DIFF(LEAD(datetime)
    OVER (PARTITION BY d.ip_address,advertiser,order_number ORDER BY datetime ASC),d.datetime  , SECOND)<=4 THEN 'Duplicate' ELSE 'Keep' END delta_followed_by_click,
    LAG(datetime)
    OVER (PARTITION BY d.ip_address,advertiser,order_number ORDER BY datetime ASC) AS preceding_click,
    CASE WHEN TIMESTAMP_DIFF(d.datetime  , LAG(datetime)
    OVER (PARTITION BY d.ip_address,advertiser,order_number ORDER BY datetime ASC), SECOND)<=4 THEN 'Duplicate' ELSE 'Keep' END delta_preceding_click,
    FROM data_corrected d
ORDER BY d.datetime desc

Hope this helps in achieving your results.

user_34171340
Related