Generate additional row after last entry for each company

I have a table with the columns date, company, value.

The dates are always the day of the end of the quarter, so 2020-12-31, 2021-03-31, etc.

I need a query that shows a boolean value called "write_off" on the subsequent quarter after the latest entry for each company.

For example, if the data is:

2020-12-31 CompanyA 100
2021-03-31 CompanyA 120
2021-06-30 CompanyA 120

Then the query should show:

2020-12-31 CompanyA 100  false
2021-03-31 CompanyA 120  false
2021-06-30 CompanyA 120  false
2021-09-30 CompanyA null true

I've tried to use LEAD() to create a "next quarter" row, but I can't make it work.


  • SELECT date, company, value, false AS write_off FROM tbl
    (  -- parentheses required
    SELECT DISTINCT ON (company)
          (date + interval '3 month')::date, company
         , null AS value, true AS write_off
    FROM   tbl
    ORDER  BY company, date DESC NULLS LAST

    We need the extra parentheses, else ORDER BY would apply to the whole UNION ALL query (the combined set).

    Depending on undisclosed cardinalities, there may be faster (optimized) query variants to get the latest row per company, especially if there are many companies and many rows per company. See: