CODEWITHSUNDEEP
sqlteradata
user3150267
user3150267

Reputation: 11

SQL time range for duplicate visits

Simply looking to find customer who visited a certain page with two hours and return those users.

The table columns would be something like customer Id, page name & date. While I am able to find the id's who visited a certain page name more than once and the dates visited I am having trouble applying a rolling two hour period to the unique id's.

Any suggestions?

Upvotes: 0

Views: 44

Answers (2)

dnoeth
dnoeth

Reputation: 60462

You need to check the previous row's timestamp and compare it to the current, like this:

SELECT DISTINCT custid 
...
QUALIFY 
   MAX(ts_col) -- previous timestamp
   OVER (PARTITION BY custid -- for each customer
         ORDER BY ts_col
         ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
   + INTERVAL '2' HOUR > ts_col

Upvotes: 4

StelioK
StelioK

Reputation: 1781

Try something like this

WITH ROW AS 
(
    SELECT *
        , ROW_NUMBER() OVER (ORDER BY customerid, [page name], date ) AS RN 
    FROM #YOURTABLE
)
    SELECT DISTINCT R1.customerid, R1.[page name], R1.date
    , (1.0*datediff(second, r1.date, r2.date)/(3600)) [delta (hrs)]
                FROM ROW R1
                    JOIN ROW R2
                    ON (    R1.RN = R2.RN - 1
                                and r1.customerid = r2.customerid
                                AND R1.[page name]= R2.[page name]                                  
                        )
                        order by customerid, [page name], DATE;

This will give you deltas between each event per customerid and page name date, using this logic will get you in the right direction. It's very hard to speculate without any of your code or data examples.

Upvotes: 0

Related Questions