How to use DATEDIFF with a Window Function in SQL-CodePudding

When I run the following query:

select 
  post_visid_high || ':' || post_visid_low as visitor_id
 , datediff(minute, lag(date_time), date_time) over (partition by visitor_id order by date_time asc)
from adobe_data

I get

Invalid function type [DATEDIFF] for window function.
Invalid function type [TIMEDIFF] for window function.

I can rewrite the query as

select 
  post_visid_high || ':' || post_visid_low as visitor_id
  , lag(date_time) over (partition by visitor_id order by date_time asc) as previous_date
  , datediff(minute, previous_date, date_time) as difference_in_minutes
from adobe_data

But I am wondering if there is a better way to do this?

CodePudding user response：

The issue is placement of ():

select 
  post_visid_high || ':' || post_visid_low as visitor_id
 , datediff(minute, lag(date_time), date_time) over (partition by visitor_id
                                                    order by date_time asc)
from adobe_data

select 
  post_visid_high || ':' || post_visid_low as visitor_id
 , datediff(minute, lag(date_time) over (partition by visitor_id
                                         order by date_time asc), date_time)
from adobe_data

CodePudding user response：

You've put the window outside of the datediff, but it should be outside of the lag.

datediff(minute, lag(date_time), date_time) over (partition by visitor_id order by date_time asc)

Becomes...

datediff(minute, lag(date_time) over (partition by visitor_id order by date_time asc), date_time)

Also, long narrow code is easier to read and validate than short wise code...

datediff(
    minute,
    lag(date_time) over (partition by visitor_id order by date_time asc),
    date_time
)

Or even...

datediff(
    minute,
    lag(date_time) over (
         partition by visitor_id
             order by date_time asc
    ),
    date_time
)

It's also more friendly to diff tools, such as used by git.