CREATE TABLE occurrence ( animal text, total int, from_date date );

INSERT INTO occurrence (animal, total, from_date) VALUES ('cat',1,'1800-01-01'), ('bird',1,'1846-06-13'), ('dog',1,'1865-05-24'), ('dog',11,'1869-05-02'), ('dog',1,'1869-05-06'), ('dog',1,'1869-05-12'), ('cat',1,'1870-01-01'), ('dog',2,'1872-06-06'), ('dog',1,'1876-04-24'), ('dog',1,'1882-05-01'), ('dog',1,'1883-04-30'), ('dog',1,'1884-05-11'), ('cat',1,'1884-06-22'), ('dog',1,'1885-04-23'), ('cat',1,'1885-07-08'), ('cat',1,'1885-07-10'), ('dog',1,'1890-05-25'), ('dog',1,'1894-05-18'), ('bird',1,'1894-06-16'), ('bird',1,'1894-06-16'), ('cat',1,'1895-05-14'), ('dog',1,'1896-06-01'), ('dog',1,'1900-05-24'), ('dog',1,'1903-05-27'), ('cat',1,'1905-03-14'), ('bird',3,'1905-04-23'), ('dog',1,'1905-05-19'), ('dog',1,'1905-05-19'), ('dog',1,'1905-05-19'), ('dog',3,'1905-06-12'), ('cat',1,'1905-06-27'), ('dog',1,'1905-06-29'), ('bird',1,'1906-05-15'), ('bird',1,'1906-05-15'), ('bird',1,'1906-05-15'), ('bird',1,'1907-05-12'), ('bird',1,'1907-05-12'), ('bird',1,'1907-05-12'), ('bird',1,'1909-06-21'), ('bird',1,'1909-06-21'), ('bird',3,'1910-05-14'), ('bird',4,'1910-05-21'), ('bird',1,'1910-05-21'), ('dog',3,'1910-05-22'), ('dog',1,'1910-05-27'), ('dog',1,'1910-05-27'), ('dog',1,'1910-05-27'), ('dog',1,'1912-05-28'), ('dog',1,'1912-06-11'), ('dog',1,'1913-05-12'), ('dog',1,'1913-05-12'), ('bird',2,'1913-05-14'), ('bird',2,'1913-05-14'), ('dog',1,'1914-01-01'), ('cat',3,'1914-07-03'), ('cat',1,'1914-07-06'), ('cat',1,'1916-05-28'), ('dog',1,'1916-05-29'), ('dog',1,'1916-05-29'), ('dog',1,'1916-05-29'), ('cat',1,'1916-06-14'), ('bird',1,'1916-06-18'), ('dog',1,'1917-05-01'), ('dog',1,'1917-05-25'), ('dog',1,'1918-01-01'), ('dog',3,'1918-05-20'), ('dog',1,'1919-05-24'), ('dog',1,'1919-05-25'), ('dog',2,'1919-05-29'), ('bird',1,'1919-06-09'), ('bird',1,'1919-06-09'), ('dog',2,'1920-05-20'), ('bird',1,'1920-05-29'), ('bird',1,'1920-05-29'), ('dog',1,'1921-05-05'), ('dog',1,'1921-05-09'), ('dog',1,'1922-05-08'), ('cat',1,'1922-06-15'), ('dog',1,'1922-07-02'), ('dog',1,'1923-04-04'), ('dog',1,'1923-05-29'), ('bird',1,'1923-06-25'), ('dog',1,'1924-01-01'), ('dog',1,'1924-05-17'), ('bird',1,'1925-04-08'), ('bird',1,'1925-04-08'), ('bird',1,'1926-06-28'), ('bird',2,'1927-05-21'), ('bird',2,'1927-05-21'), ('bird',1,'1927-05-26'), ('dog',1,'1928-06-12'), ('cat',1,'1931-05-23'), ('dog',1,'1932-05-16'), ('bird',2,'1932-06-15'), ('bird',1,'1932-06-19'), ('bird',3,'1932-06-19'), ('bird',1,'1933-07-22'), ('cat',1,'1934-06-02'), ('bird',1,'1934-06-04'), ('bird',1,'1934-06-05'), ('bird',1,'1934-06-08'), ('bird',1,'1934-06-09'), ('cat',1,'1934-06-10'), ('bird',1,'1934-06-13'), ('bird',1,'1937-05-29'), ('cat',1,'1937-06-07'), ('bird',1,'1938-07-03'), ('cat',1,'1938-07-07'), ('dog',1,'1939-01-01'), ('dog',3,'1939-05-09'), ('dog',1,'1939-05-21'), ('dog',1,'1939-05-23'), ('dog',1,'1939-05-29'), ('cat',1,'1939-06-11'), ('bird',1,'1939-06-11'), ('dog',2,'1940-05-03'), ('dog',3,'1940-05-03'), ('dog',1,'1941-05-16'), ('cat',1,'1942-07-12'), ('cat',1,'1942-07-13'), ('dog',1,'1943-04-26'), ('dog',1,'1943-05-09'), ('bird',1,'1943-05-13'), ('dog',1,'1944-05-06'), ('dog',1,'1944-05-06'), ('cat',1,'1945-07-02'), ('dog',1,'1945-07-03'), ('dog',1,'1946-04-12'), ('dog',1,'1946-05-08'), ('bird',1,'1946-05-12'), ('bird',1,'1946-05-12'), ('bird',2,'1946-05-12'), ('bird',1,'1946-07-25'), ('bird',1,'1947-06-01'), ('cat',1,'1947-06-19'), ('bird',1,'1949-04-29'), ('dog',1,'1949-05-04'), ('bird',1,'1949-05-15'), ('bird',1,'1949-06-05'), ('cat',2,'1949-07-16'), ('bird',3,'1950-04-17'), ('cat',1,'1950-06-24'), ('dog',1,'1951-05-08'), ('bird',1,'1956-05-27'), ('cat',1,'1957-06-07'), ('dog',1,'1958-01-01'), ('bird',1,'1958-06-25'), ('bird',1,'1958-07-11'), ('cat',1,'1958-07-20'), ('bird',15,'1960-05-22'), ('bird',1,'1960-05-22'), ('dog',1,'1960-05-29'), ('dog',1,'1960-06-11'), ('cat',2,'1960-06-26'), ('dog',0,'1960-09-22'), ('dog',1,'1961-04-12'), ('cat',1,'1961-06-22'), ('bird',1,'1961-07-16'), ('cat',1,'1962-06-29'), ('cat',2,'1962-07-06'), ('bird',1,'1963-05-23'), ('bird',1,'1963-06-21'), ('bird',1,'1963-06-25'), ('dog',2,'1963-07-17'), ('dog',0,'1963-07-17'), ('cat',1,'1964-04-02'), ('bird',1,'1964-05-17'), ('dog',1,'1964-05-26'), ('bird',1,'1964-06-05'), ('bird',1,'1964-06-08'), ('bird',1,'1964-06-15'), ('cat',1,'1964-06-26'), ('bird',1,'1964-07-01'), ('cat',1,'1964-07-09'), ('bird',1,'1965-06-11'), ('bird',1,'1965-07-11'), ('dog',1,'1966-05-16'), ('cat',1,'1966-06-18'), ('cat',1,'1966-07-01'), ('dog',0,'1966-08-08'), ('dog',0,'1966-08-18'), ('dog',1,'1967-01-01'), ('dog',1,'1967-05-05'), ('bird',1,'1967-05-07'), ('dog',1,'1967-05-08'), ('bird',1,'1967-05-30'), ('bird',1,'1967-06-18'), ('bird',1,'1967-06-25'), ('dog',1,'1967-06-27'), ('bird',1,'1967-06-28'), ('cat',1,'1967-07-16'), ('cat',1,'1967-07-16'), ('dog',1,'1968-04-24'), ('bird',1,'1968-05-16'), ('bird',1,'1968-05-23'), ('bird',1,'1968-05-25'), ('dog',2,'1968-05-30'), ('cat',1,'1968-06-19'), ('dog',1,'1969-05-08'), ('dog',2,'1969-05-14'), ('bird',1,'1969-06-07'), ('bird',1,'1969-06-08'), ('bird',1,'1969-06-08'), ('bird',3,'1969-06-08'), ('bird',2,'1969-06-11'), ('bird',1,'1969-06-11'), ('bird',1,'1969-06-29'), ('bird',1,'1969-06-29'), ('cat',1,'1969-07-21'), ('cat',1,'1969-07-21'), ('bird',3,'1970-05-24'), ('bird',3,'1970-05-24'), ('bird',2,'1970-06-07'), ('bird',2,'1970-06-13'), ('bird',1,'1970-06-24'), ('bird',0,'1970-09-16'), ('dog',1,'1971-04-22'), ('dog',1,'1971-05-04'), ('dog',1,'1971-05-05'), ('dog',1,'1971-05-05'), ('dog',1,'1971-05-06'), ('bird',1,'1972-05-06'), ('bird',5,'1972-05-07'), ('cat',1,'1972-05-19'), ('bird',1,'1972-05-19'), ('bird',1,'1972-05-20'), ('dog',1,'1972-05-24'), ('cat',1,'1972-07-29'), ('bird',1,'1973-05-05'), ('bird',1,'1973-05-06'), ('dog',2,'1973-05-17'), ('bird',1,'1973-05-18'), ('dog',1,'1973-05-21'), ('dog',1,'1973-05-28'), ('cat',1,'1973-06-24'), ('bird',4,'1974-05-16'), ('dog',1,'1974-05-23'), ('dog',4,'1974-05-24'), ('cat',1,'1974-06-16'), ('cat',3,'1974-06-16'), ('bird',1,'1974-07-04'), ('cat',1,'1975-05-25'), ('cat',2,'1975-05-25'), ('cat',1,'1975-06-23'), ('cat',1,'1975-06-23'), ('cat',1,'1975-07-02'), ('bird',2,'1975-07-08'), ('bird',12,'1976-05-23'), ('bird',12,'1976-05-23'), ('bird',4,'1976-05-30'), ('bird',4,'1976-05-30'), ('cat',1,'1976-07-20'), ('dog',1,'1977-01-01'), ('dog',1,'1977-04-26'), ('cat',1,'1977-05-09'), ('bird',3,'1977-06-06'), ('bird',7,'1977-06-06'), ('bird',3,'1977-06-07'), ('bird',1,'1977-06-07'), ('bird',6,'1977-06-07'), ('bird',1,'1977-06-09'), ('cat',2,'1977-06-20'), ('cat',3,'1977-06-20'), ('cat',2,'1977-06-22'), ('cat',1,'1977-06-22'), ('cat',1,'1977-06-24'), ('cat',1,'1977-06-24'), ('cat',1,'1977-07-21'), ('bird',1,'1978-05-18'), ('bird',4,'1978-05-18'), ('dog',1,'1978-05-27'), ('cat',1,'1978-05-28'), ('cat',1,'1978-05-28'), ('dog',1,'1978-05-30'), ('cat',2,'1978-05-30'), ('dog',1,'1978-05-31'), ('dog',3,'1978-06-01'), ('bird',1,'1978-06-15'), ('cat',1,'1978-06-15'), ('cat',4,'1978-06-15'), ('bird',1,'1978-06-22'), ('cat',2,'1978-06-22'), ('cat',2,'1978-06-23'), ('cat',10,'1978-06-24'), ('cat',1,'1978-07-06'), ('cat',1,'1978-07-06'), ('cat',1,'1978-07-09'), ('cat',1,'1978-07-09');

create function get_first(p_input date) returns daterange as $$ select daterange(date_trunc('month', p_input)::date, date_trunc('month', p_input)::date + 10, '[]') $$ language sql immutable;

create function get_second(p_input date) returns daterange as $$ select daterange(date_trunc('month', p_input)::date + 11, date_trunc('month', p_input)::date + 20, '[]') $$ language sql immutable;

create function get_third(p_input date) returns daterange as $$ select daterange(date_trunc('month', p_input)::date + 21, (date_trunc('month', p_input) + interval '1 month')::date, '[)') $$ language sql immutable;

select animal, count(*) filter (where get_first(from_date) @> from_date) as start, count(*) filter (where get_second(from_date) @> from_date) as mid, count(*) filter (where get_third(from_date) @> from_date) as "end" from occurrence group by animal;

-- intermediate result showing the values as rows, rather than columns select x.* from ( select animal, to_char(from_date, 'yyyy-mm') as month, count(*) filter (where get_first(from_date) @> from_date) as start, count(*) filter (where get_second(from_date) @> from_date) as mid, count(*) filter (where get_third(from_date) @> from_date) as "end" from occurrence group by animal, month ) t cross join lateral ( values (animal, start, 'start'), (animal, mid, 'mid'), (animal, "end", 'end') ) x (animal, num, section) --on true order by x.animal

-- this one picks the highest "num" for each animal select distinct on (x.animal) x.* from ( select animal, to_char(from_date, 'yyyy-mm') as month, count(*) filter (where get_first(from_date) @> from_date) as start, count(*) filter (where get_second(from_date) @> from_date) as mid, count(*) filter (where get_third(from_date) @> from_date) as "end" from occurrence group by animal, month ) t cross join lateral ( values (animal, start, month, 'start'), (animal, mid, month, 'mid'), (animal, "end", month, 'end') ) x (animal, num, month, section) --on true order by x.animal, num desc