By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table check_ins (
id serial primary key,
check_in_date timestamp without time zone,
goal_id text,
user_id text
);
INSERT INTO check_ins (user_id, goal_id, check_in_date)
VALUES
('colt', '40365fa0', '2019-01-07 15:35:53'),
('colt', 'd31efe70', '2019-01-11 15:35:52'),
('berry', 'be2fcd50', '2019-01-12 15:35:51'),
('colt', 'e754d050', '2019-01-13 15:17:16'),
('colt', '9c87a7f0', '2019-01-14 15:35:54'),
('colt', 'ucgtdes0', '2019-01-15 12:30:59');
CREATE TABLE
INSERT 0 6
select distinct on (user_id) user_id, count(distinct check_in_date::date) as num_days
from (select ci.*,
dense_rank() over (partition by user_id order by check_in_date::date) as seq
from check_ins ci
) ci
group by user_id, check_in_date::date - seq * interval '1 day'
order by user_id, num_days desc
| user_id | num_days |
|---|---|
| berry | 1 |
| colt | 3 |
SELECT 2