Grouping data by year based on two timestamp columns
With generate_series() in a LATERAL join (Postgres 9.3+) and date_trunc(), this can be short &amp; simple:

SELECT EXTRACT(YEAR FROM yr)::text AS year, name, count(*) AS ct
FROM   data
     , generate_series(date_trunc('year', created)  -- LATERAL join
                     , COALESCE(deleted, localtimestamp)
                     , interval '1 year') yr
GROUP  BY yr, name
ORDER  BY yr, ct DESC;


That's all. Returns results for all years between the earliest created and the current year.
The trick is to generate one row for each year the base row overlaps with, before aggregating.

Alternative:

SELECT EXTRACT(YEAR FROM yr) AS year, d.name, count(*) AS count
FROM   generate_series ((SELECT date_trunc('year', min(created)) FROM data)
                       , localtimestamp, interval '1 year') yr
JOIN   data d ON d.created &lt; yr::timestamp + interval '1 year'
            AND (d.deleted &gt; yr::timestamp OR d.deleted IS NULL)
GROUP  BY yr, d.name
ORDER  BY count(*) DESC;


This generates the whole range of years before joining. May be more convenient to compute numbers for a manual selection of years.

Related:


Find overlapping date ranges in PostgreSQL
What is the difference between LATERAL and a subquery in PostgreSQL?


If you need to optimize performance for a small set of years out of a much bigger table, a GiST index on a tsrange type would be an option:


Optimizing queries on a range of timestamps (two columns)