SQL Query:

WITH YearlyApprovals AS (
    -- Calculate the number of approvals for each year within the last 20 calendar years
    SELECT
        EXTRACT(YEAR FROM p.approval_date) AS approval_year,
        COUNT(*) AS approval_count
    FROM
        orange_book_products p
    WHERE
        p.approval_date IS NOT NULL
        AND EXTRACT(YEAR FROM p.approval_date) BETWEEN EXTRACT(YEAR FROM CURRENT_DATE) - 19 AND EXTRACT(YEAR FROM CURRENT_DATE)
    GROUP BY
        EXTRACT(YEAR FROM p.approval_date)
),
AllYears AS (
    -- Generate a series of all calendar years for the last 20 years
    SELECT generate_series(
        EXTRACT(YEAR FROM CURRENT_DATE) - 19, -- Start year (20 years ago from current year)
        EXTRACT(YEAR FROM CURRENT_DATE)       -- End year (current year)
    ) AS year
)
-- Join the generated years with the calculated approval counts
-- Use COALESCE to show 0 for years with no approvals
SELECT
    ay.year AS approval_year,
    COALESCE(ya.approval_count, 0) AS approval_count
FROM
    AllYears ay
LEFT JOIN
    YearlyApprovals ya ON ay.year = ya.approval_year
ORDER BY
    ay.year -- Order chronologically
LIMIT 20; -- Limit to ensure only 20 rows (one for each year in the range)