CODEWITHSUNDEEP
sqlpostgresqldategroup-byaverage
Daniel Maida
Daniel Maida

Reputation: 31

How do I get the average date interval of a column in SQL?

I have a table of user interactions on a web site and I need to calculate the average time between interactions of each user. To make it more simple to understand, here's some records of the table:

Table of user interactions

Where the first column is the user id and the second is the interaction time. The results that I need is the average time between interactions of each user. Example:

I've already tried to use window functions, but i couldn't get the average because PostgreSQL doesn't let me use GROUP BY or AVG on window functions, I could get the intervals using the following command, but couldn't group it based on the user id.

SELECT INTERACTION_DATE - LAG(INTERACTION_DATE ) OVER (ORDER BY INTERACTION_DATE )

So, I decided to create my own custom function and after that, create a custom aggregate function to do this, and use this function on a group by clause:

CREATE OR REPLACE FUNCTION DATE_INTERVAL(TIMESTAMP)  
     RETURNS TABLE (USER_INTERVALS INTERVAL) 
AS $$
  SELECT $1 - LAG($1) OVER (ORDER BY $1) 
$$
LANGUAGE SQL
IMMUTABLE;

But this function only return several rows with one column with null value.

Is there a better way to do this?

Upvotes: 1

Views: 1220

Answers (2)

user330315
user330315

Reputation:

You need to first calculate the difference between the interactions for each row (and user), then you can calculate the average on that:

select user_id, avg(interaction_time)
from (
   select user_id, 
          interaction_date - lag(interaction_date) over (partition by user_id order by interaction_date) as interaction_time
   from the_table
) t
group by user_id;

Upvotes: 3

Ludovic Feltz
Ludovic Feltz

Reputation: 11916

Encapsule your first query then compute the average:

SELECT AVG(InteractionTime) FROM (
    SELECT INTERACTION_DATE - LAG(INTERACTION_DATE ) OVER (ORDER BY INTERACTION_DATE ) AS InteractionTime
)

Upvotes: 0

Related Questions