Generate additional row after last entry for each company

Question

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.

Answer 1

SELECT date, company, value, false AS write_off FROM tbl
 
UNION ALL
(  -- 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:

• Select first row in each GROUP BY group?
• Optimize GROUP BY query to retrieve latest row per user