Postgresql: Trying to get Average of Counts for the last 10 ten days

Question

Select avg(last_10_count) AS last_10_avg
(Select count(*)
from dim_user
where effective_date ::date > current_date -10
group by effective_date ::date) AS last_10_count

When I just run the inline query, I get the desired result, but when I run the whole query it throws the following error:

ERROR: function avg(record) does not exist
LINE 1: Select avg(last_10_count) AS last_10_avg
HINT: NO function matches the given name and arguement types.
      You might need to add explicit type casts.
************Error***************
ERROR: function avg(record)  does not exit
SQL state: 42883

Solution

Try this one

Select avg(last_10_count) AS last_10_avg from 
(Select count(*) as last_10_count
from dim_user
where effective_date::date  > current_date -10
group by effective_date :: date) Z