CODEWITHSUNDEEP
postgresql
Vinicius Esperança
Vinicius Esperança

Reputation: 39

How to calculate mean date difference with Postgres

I have a table on my postgres database, which has two main fields: agent_id and quoted_at.

I need to group my data by agent_id, and calculate the mean difference among all quoted_at.

So, for example, if I have the following rows:

agent_id | quoted_at
---------+-----------
       1 | 2020-04-02
       1 | 2020-04-04
       1 | 2020-04-05

The mean difference would be calculated as:

What I want to see after grouping the info is:

agent_id | mean
---------+---------
       1 | 1.5 days

I know, by the end, I just need to calculate (last - first) / (#_occurrences - 1) It is just not really clear how (and if) it is possible to do that using a single query on Postgres.

Upvotes: 0

Views: 45

Answers (1)

Mike Organek
Mike Organek

Reputation: 12484

Use the lag() window function to calculate your differences. Once you have those differences, use the avg() aggregation function.

with diffs as (
  select agent_id, quoted_at, 
         quoted_at - lag(quoted_at) over (partition by agent_id
                                              order by quoted_at) as diff_days
    from your_table
)
select agent_id, avg(diff_days) as mean
  from diffs
 where diff_days is not null;

The check for null diff_days is necessary since the diff_days for the first record for an agent is null, and you do not want that in the avg() aggregation.

Upvotes: 1

Related Questions