SQL Average of Difference in Days

Question

I'm exactly doing the same thing as here Date Difference between consecutive rows

However I want the result to show me avg of difference in days per every account_number and not one row for every difference in days.

Something like this.

When I add avg(datediff(d1,d2)), i'm getting the following error:

[Code: -112, SQL State: 42607] The operand of the column function "AVG" includes a column function, a scalar fullselect, or a subquery.. SQLCODE=-112, SQLSTATE=42607, DRIVER=4.22.29

PS:

1. I was unable to add comment on the same page of the above link as my stack overflow score is low & it doesn't allow me to comment.
2. I'm using db2 so instead of datediff I use timestampdiff. Dint want to confuse by writing it above.
3. Apologies if its not clear. Do let me know what can I do better

Answer 1

It's convenient to use the lead OLAP function in this case:

with tab (ID, Account, Date) as (values
  (1, 1001, date('2011-09-10'))
, (2, 2001, date('2011-09-01'))
, (3, 2001, date('2011-09-03'))
, (4, 1001, date('2011-09-12'))
, (5, 3001, date('2011-09-18'))
, (6, 1001, date('2011-09-20'))
)
select account, avg(days(date_next) - days(date)) avg_diff
from (
select id, account, date, lead(date) over (partition by account order by date) date_next
from tab
)
group by account;