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;
✓
hidden batch(es)
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;
✓
hidden batch(es)
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;
✓
hidden batch(es)
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;
animal
start
mid
end
dog
40
24
39
cat
23
24
25
bird
38
44
31
…
hidden batch(es)
-- 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
animal
num
section
bird
0
end
bird
1
mid
bird
0
start
bird
1
end
bird
0
mid
bird
0
start
bird
0
end
bird
3
mid
bird
0
start
bird
2
end
bird
0
mid
bird
0
start
bird
0
end
bird
1
mid
bird
0
start
bird
1
start
bird
0
mid
bird
0
end
bird
0
start
bird
3
mid
bird
0
end
bird
2
end
bird
1
mid
bird
0
start
bird
0
start
bird
0
mid
bird
1
end
bird
0
end
bird
0
mid
bird
1
start
bird
4
start
bird
1
mid
bird
0
end
bird
0
end
bird
2
mid
bird
0
start
bird
1
end
bird
2
mid
bird
0
start
bird
0
end
bird
0
mid
bird
2
start
bird
1
start
bird
0
mid
bird
0
end
bird
1
end
bird
0
mid
bird
0
start
bird
0
end
bird
1
mid
bird
2
start
bird
1
start
bird
0
mid
bird
0
end
bird
0
start
bird
0
mid
bird
1
end
bird
1
end
bird
0
mid
bird
0
start
bird
0
start
bird
0
mid
bird
4
end
bird
0
end
bird
1
mid
bird
0
start
bird
0
end
bird
2
mid
bird
0
start
bird
0
end
bird
2
mid
bird
0
start
bird
0
start
bird
3
mid
bird
0
end
bird
0
end
bird
1
mid
bird
2
start
bird
1
end
bird
1
mid
bird
1
start
bird
0
end
bird
0
mid
bird
1
start
bird
0
end
bird
2
mid
bird
0
start
bird
2
start
bird
0
mid
bird
0
end
bird
0
start
bird
0
mid
bird
1
end
bird
0
end
bird
0
mid
bird
1
start
bird
0
end
bird
2
mid
bird
2
start
bird
0
end
bird
1
mid
bird
0
start
bird
1
end
bird
0
mid
bird
0
start
bird
1
end
bird
0
mid
bird
0
start
bird
2
end
bird
0
mid
bird
0
start
bird
0
start
bird
1
mid
bird
0
end
bird
6
start
bird
0
mid
bird
0
end
bird
0
start
bird
0
mid
bird
1
end
bird
1
start
bird
0
mid
bird
0
end
bird
0
end
bird
0
mid
bird
1
start
bird
1
start
bird
0
mid
bird
0
end
bird
0
start
bird
1
mid
bird
1
end
bird
2
end
bird
0
mid
bird
0
start
bird
1
end
bird
1
mid
bird
0
start
bird
1
start
bird
0
mid
bird
1
end
bird
0
start
bird
1
mid
bird
0
end
bird
0
end
bird
1
mid
bird
0
start
bird
0
end
bird
0
mid
bird
1
start
bird
0
end
bird
3
mid
bird
0
start
bird
0
end
bird
3
mid
bird
0
start
bird
1
end
bird
0
mid
bird
0
start
bird
0
start
bird
1
mid
bird
0
end
bird
2
end
bird
1
mid
bird
0
start
bird
6
start
bird
0
mid
bird
2
end
bird
0
start
bird
1
mid
bird
0
end
cat
0
start
cat
1
mid
cat
0
end
cat
0
start
cat
1
mid
cat
0
end
cat
1
start
cat
0
mid
cat
0
end
cat
0
start
cat
1
mid
cat
0
end
cat
0
start
cat
0
mid
cat
1
end
cat
1
start
cat
0
mid
cat
0
end
cat
0
start
cat
2
mid
cat
4
end
cat
0
start
cat
1
mid
cat
0
end
cat
0
start
cat
0
mid
cat
1
end
cat
0
start
cat
1
mid
cat
0
end
cat
1
start
cat
0
mid
cat
0
end
cat
0
start
cat
1
mid
cat
0
end
cat
0
start
cat
1
mid
cat
0
end
cat
0
start
cat
0
mid
cat
1
end
cat
0
start
cat
1
mid
cat
0
end
cat
2
start
cat
0
mid
cat
0
end
cat
1
start
cat
0
mid
cat
0
end
cat
0
start
cat
0
mid
cat
1
end
cat
1
start
cat
0
mid
cat
0
end
cat
1
start
cat
0
mid
cat
0
end
cat
2
start
cat
0
mid
cat
0
end
cat
0
start
cat
0
mid
cat
1
end
cat
0
start
cat
1
mid
cat
0
end
cat
0
start
cat
0
mid
cat
2
end
cat
0
start
cat
0
mid
cat
3
end
cat
1
start
cat
0
mid
cat
0
end
cat
4
start
cat
0
mid
cat
0
end
cat
0
start
cat
0
mid
cat
2
end
cat
0
start
cat
2
mid
cat
0
end
cat
1
start
cat
0
mid
cat
0
end
cat
2
start
cat
0
mid
cat
0
end
cat
0
start
cat
2
mid
cat
0
end
cat
0
start
cat
1
mid
cat
0
end
cat
0
start
cat
0
mid
cat
1
end
cat
0
start
cat
0
mid
cat
1
end
cat
0
start
cat
0
mid
cat
1
end
cat
0
start
cat
1
mid
cat
0
end
cat
1
start
cat
0
mid
cat
0
end
cat
1
start
cat
0
mid
cat
0
end
cat
0
start
cat
0
mid
cat
1
end
cat
0
start
cat
0
mid
cat
1
end
cat
0
start
cat
0
mid
cat
1
end
cat
0
start
cat
2
mid
cat
0
end
cat
0
start
cat
2
mid
cat
0
end
cat
1
start
cat
0
mid
cat
0
end
cat
0
start
cat
1
mid
cat
0
end
cat
1
start
cat
0
mid
cat
0
end
cat
1
start
cat
0
mid
cat
0
end
cat
0
start
cat
2
mid
cat
3
end
dog
0
start
dog
3
mid
dog
0
end
dog
0
end
dog
1
mid
dog
0
start
dog
1
start
dog
0
mid
dog
0
end
dog
0
start
dog
0
mid
dog
1
end
dog
1
start
dog
0
mid
dog
0
end
dog
0
end
dog
0
mid
dog
2
start
dog
0
start
dog
1
mid
dog
0
end
dog
1
end
dog
0
mid
dog
0
start
dog
1
end
dog
0
mid
dog
0
start
dog
0
start
dog
2
mid
dog
0
end
dog
0
end
dog
0
mid
dog
1
start
dog
0
end
dog
0
mid
dog
1
start
dog
1
start
dog
0
mid
dog
0
end
dog
0
start
dog
0
mid
dog
4
end
dog
1
start
dog
0
mid
dog
0
end
dog
0
start
dog
2
mid
dog
1
end
dog
0
end
dog
0
mid
dog
1
start
dog
0
start
dog
0
mid
dog
1
end
dog
0
end
dog
1
mid
dog
1
start
dog
0
start
dog
0
mid
dog
1
end
dog
1
start
dog
0
mid
dog
0
end
dog
1
start
dog
0
mid
dog
0
end
dog
0
start
dog
0
mid
dog
1
end
dog
0
end
dog
0
mid
dog
1
start
dog
1
end
dog
0
mid
dog
0
start
dog
1
end
dog
0
mid
dog
0
start
dog
0
end
dog
1
mid
dog
0
start
dog
0
end
dog
0
mid
dog
1
start
dog
0
end
dog
2
mid
dog
0
start
dog
1
start
dog
0
mid
dog
0
end
dog
0
end
dog
1
mid
dog
0
start
dog
1
start
dog
0
mid
dog
0
end
dog
0
start
dog
0
mid
dog
1
end
dog
0
start
dog
1
mid
dog
0
end
dog
0
start
dog
1
mid
dog
0
end
dog
0
end
dog
0
mid
dog
1
start
dog
1
start
dog
0
mid
dog
0
end
dog
0
end
dog
0
mid
dog
4
start
dog
0
start
dog
1
mid
dog
0
end
dog
2
end
dog
0
mid
dog
0
start
dog
0
end
dog
1
mid
dog
2
start
dog
0
end
dog
0
mid
dog
1
start
dog
1
end
dog
0
mid
dog
0
start
dog
1
start
dog
0
mid
dog
0
end
dog
0
start
dog
0
mid
dog
1
end
dog
1
end
dog
0
mid
dog
0
start
dog
1
end
dog
0
mid
dog
0
start
dog
2
start
dog
0
mid
dog
0
end
dog
0
start
dog
0
mid
dog
1
end
dog
1
start
dog
0
mid
dog
1
end
dog
0
start
dog
0
mid
dog
3
end
dog
1
end
dog
0
mid
dog
0
start
dog
0
end
dog
0
mid
dog
1
start
dog
1
end
dog
0
mid
dog
0
start
dog
0
start
dog
1
mid
dog
1
end
dog
0
end
dog
1
mid
dog
1
start
dog
1
end
dog
0
mid
dog
0
start
dog
0
start
dog
0
mid
dog
1
end
dog
0
end
dog
0
mid
dog
2
start
dog
0
end
dog
1
mid
dog
0
start
dog
0
start
dog
1
mid
dog
0
end
dog
0
end
dog
1
mid
dog
0
start
dog
0
end
dog
0
mid
dog
1
start
dog
2
start
dog
0
mid
dog
0
end
dog
0
start
dog
0
mid
dog
1
end
dog
0
end
dog
0
mid
dog
1
start
dog
0
end
dog
0
mid
dog
1
start
dog
0
start
dog
0
mid
dog
3
end
dog
0
end
dog
0
mid
dog
1
start
dog
0
start
dog
0
mid
dog
3
end
dog
1
start
dog
1
mid
dog
2
end
…
hidden batch(es)
-- 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