How to find the increased amount each month?

Question

http://sqlfiddle.com/#! 17/5184a5/3/0

Dice:

create table x (id int8 primary key, fk int8, date date, qty int4);
insert into x values (292203, 10679, '2019-06-15', 65)
                    ,(300207, 10679, '2019-06-22', 66) 
                    ,(316167, 10679, '2019-07-06', 66)
                    ,(324189, 10679, '2019-07-13', 68)
                    ,(333166, 10679, '2019-07-20', 68)
                    ,(1207633, 10679, '2020-02-15', 68)
                    ,(1421301, 10679, '2020-04-13', 78)                         
                    ,(1245694, 106153, '2020-02-29', 7328)
                    ,(1273500, 106153, '2020-03-07', 7367)
                    ,(1302662, 106153, '2020-03-14', 7409)
                    ,(1324696, 106153, '2020-03-21', 7456)
                    ,(1356181, 106153, '2020-03-28', 7485)
                    ,(1384658, 106153, '2020-04-06', 7526)
                    ,(1412600, 106153, '2020-04-13', 7556)
                    ,(1444914, 106153, '2020-04-21', 7623);

As days pass, the Qty column is incremented for each fk.

The need: Discover the amount incremented each month.

The problem: In a few months there are no records corresponding to a fk, in others there are one or more records within a month.

A possible solution: Get an average of Qty (quantity) multiplied by the number of days of unregistered months.

Please review the query I made (consider the comments):

select  
    fk
    , FIRST_DAY_OF_MONTH
    , LAST_DAY_OF_MONTH
    , sum(case when min_date > previous_max_date
        then ((min_qty - previous_max_qty)::decimal / (min_date - previous_max_date) * days_until_min_date) 
        else 0
    end /*QTY_UNTIL_MIN_DATE, Precisamos fazer inferência, com base na média, aqui!!*/ 
    + max_qty - min_qty /*QTY_BETWEEN_MIN_MAX_DATE, Não devemos fazer inferência, com base na média, aqui!*/ 
    + case when next_min_date > max_date
        then ((next_min_qty - max_qty)::decimal / (next_min_date - max_date) * days_until_end_month) 
        else 0
    end /*QTY_FROM_MAX_DATE_TO_END_MONTH Precisamos fazer inferência, com base na média, aqui!*/ 
    )TOTAL_MONTH_QTY
from (
        select 
            x.fk,
            d.FIRST_DAY_OF_MONTH,
            d.LAST_DAY_OF_MONTH,
            min(x.date) - d.FIRST_DAY_OF_MONTH days_until_min_date,
            min(x.date) min_date,
            max(x.date) max_date,
            d.LAST_DAY_OF_MONTH - max(x.date) days_until_end_month,
            min(x.qty) min_qty,
            max(x.qty) max_qty,
            LAG(min(x.date)) over (w) next_min_date,
            LEAD(max(x.date)) over (w) previous_max_date,
            LAG(min(x.qty)) over (w) next_min_qty,
            LEAD(max(x.qty)) over (w) previous_max_qty              
        from                
            x right join 
                (select d.FIRST_DAY_OF_MONTH, 
                    (date_trunc('month', d.FIRST_DAY_OF_MONTH) + interval '1 month' - interval '1 day')::date LAST_DAY_OF_MONTH from 
                    (select (date_trunc('month', current_date/*2020-05-10*/) - (interval '1' month * generate_series(0,11)))::date FIRST_DAY_OF_MONTH) 
                    d) d    
                on d.FIRST_DAY_OF_MONTH = date_trunc('month', x.date)
        group by x.fk, d.FIRST_DAY_OF_MONTH, d.LAST_DAY_OF_MONTH
        window
            w as (partition by x.fk order by max(x.date) desc)
        order by d.FIRST_DAY_OF_MONTH desc
) t
group by fk, FIRST_DAY_OF_MONTH, LAST_DAY_OF_MONTH
order by FIRST_DAY_OF_MONTH desc;

The result:

|     fk | first_day_of_month | last_day_of_month |    total_month_qty |
|--------|--------------------|-------------------|--------------------|
| (null) |         2020-05-01 |        2020-05-31 |             (null) |
| 106153 |         2020-04-01 |        2020-04-30 | 119.77777777777777 |
|  10679 |         2020-04-01 |        2020-04-30 | 2.0689655172413794 |
| 106153 |         2020-03-01 |        2020-03-31 |  165.0952380952381 |
|  10679 |         2020-02-01 |        2020-02-29 |  2.413793103448276 |
| 106153 |         2020-02-01 |        2020-02-29 |                  0 |
| (null) |         2020-01-01 |        2020-01-31 |             (null) |
| (null) |         2019-12-01 |        2019-12-31 |             (null) |
| (null) |         2019-11-01 |        2019-11-30 |             (null) |
| (null) |         2019-10-01 |        2019-10-31 |             (null) |
| (null) |         2019-09-01 |        2019-09-30 |             (null) |
| (null) |         2019-08-01 |        2019-08-31 |             (null) |
|  10679 |         2019-07-01 |        2019-07-31 |                  2 |
|  10679 |         2019-06-01 |        2019-06-30 |                  1 |

• Column fk and total_month_qty should never be null.

The expected result then is:

|     fk | first_day_of_month | last_day_of_month |    total_month_qty |
|--------|--------------------|-------------------|--------------------|
| 106153 |         2020-04-01 |        2020-04-30 | 119.77777777777777 |
|  10679 |         2020-04-01 |        2020-04-30 | 2.0689655172413794 |aproximadamente
| 106153 |         2020-03-01 |        2020-03-31 |  165.0952380952381 |
|  10679 |         2020-03-01 |        2020-03-31 |  5.172413793103448 |aproximadamente
|  10679 |         2020-02-01 |        2020-02-29 |  2.413793103448276 |aproximadamente
| 106153 |         2020-02-01 |        2020-02-29 |                  0 |
|  10679 |         2020-01-01 |        2020-01-31 |                  0 |
|  10679 |         2019-12-01 |        2019-12-31 |                  0 |
|  10679 |         2019-11-01 |        2019-11-30 |                  0 |
|  10679 |         2019-10-01 |        2019-10-31 |                  0 |
|  10679 |         2019-09-01 |        2019-09-30 |                  0 |
|  10679 |         2019-08-01 |        2019-08-31 |                  0 |
|  10679 |         2019-07-01 |        2019-07-31 |                  2 |
|  10679 |         2019-06-01 |        2019-06-30 |                  1 |

Solving the problem of JOIN (fk returning null), the query I made will account to bring this information.

Thank you very much!