Getting difference of a column between first and last row with count number I have table mytable as shown below

CREATE TABLE mytable( id, created_at, total_number )
AS VALUES
    ( 17330  , '2018-05-24 19:25:29'::timestamp, 26909 ),
    ( 46331  , '2018-05-25 00:57:34',            26914 ),
    ( 72131  , '2018-05-26 00:48:12',            26944 ),
    ( 102583 , '2018-05-27 00:53:50',            26972 );


I would like to get total_number difference between first and the last

I got my first and last row with this query

(SELECT * FROM mytable 
    ORDER BY created_at ASC LIMIT 1)
    UNION
    (SELECT * FROM mytable 
    ORDER BY created_at DESC LIMIT 1)  


My query should return this results. How can I achieve it?

difference        number_of_rows   avg
(26972-26909=63)  4                63/4
Another variation. Here the function first_value is used to get both the first and last total_number (different directions):

select abs(lst - fst) as diff, number_of_rows
     , abs(lst - fst) / number_of_rows as average 
from (
    select count(1) over () as number_of_rows
         , first_value(total_number) over (order by created_at) fst
         , first_value(total_number) over (order by created_at desc) lst
    from tbl
    fetch first 1 rows only
) as t;


An alternative is to specify the window frame with unbounded following and use the function last_value:

select abs(lst - fst) as diff, number_of_rows
     , abs(lst - fst) / number_of_rows as average 
from (
    select count(1) over () as number_of_rows
         , first_value(total_number) over (order by created_at) fst
         , last_value(total_number) over (order by created_at 
                                          rows between current row 
                                                and unbounded following
                                         ) lst
    from tbl
    fetch first 1 rows only
) as t;


Perhaps it is worth noting that the default window frame is range between unbounded preceding and current row, so in the last example, we need to override that to truly see the last row. All rows in the sub-select are identical, so we can pick one randomly (used in both examples). 

I believe limit 1 now is part of the standard, but I'm used to fetch first ... so I'll stick to that 

In this situation, distinct could have been used instead of limit or fetch ...

from (
    select distinct count(1) over () as number_of_rows
         , ...
    from tbl
)