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');
INSERT 0 6
SELECT * FROM
(
WITH check_ins_dt AS
(SELECT DISTINCT check_in_date::DATE as check_in_date,
user_id
FROM check_ins)
SELECT DISTINCT ON (user_id) COUNT(*) as streak, user_id FROM
(
SELECT c.*,
ROW_NUMBER() OVER(
ORDER BY check_in_date
) - ROW_NUMBER() OVER(
PARTITION BY user_id
ORDER BY check_in_date
) as seq
FROM check_ins_dt c
)s GROUP BY user_id,seq
ORDER BY user_id,COUNT(*) desc
) q ORDER BY streak desc;
| streak | user_id |
|---|---|
| 3 | colt |
| 1 | berry |
SELECT 2