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 orders(
Date Date,
Name varchar(10)
);
INSERT INTO orders (Name,Date)
VALUES
('bob', '2014-08-10'),
('sue', '2014-08-10'),
('bob', '2014-08-11'),
('mike', '2014-08-11'),
('bob', '2014-08-12'),
('mike', '2014-08-12'),
('bob', '2014-08-05'),
('bob', '2014-08-06');
CREATE TABLE
INSERT 0 8
SELECT t.Name,COUNT(*) as frequency
FROM (SELECT o.*,
row_number() OVER (PARTITION BY Name ORDER BY Date) as seqnum
FROM orders o
) t
GROUP BY Name, date - seqnum * interval '1 day'
| name | frequency |
|---|---|
| sue | 1 |
| bob | 3 |
| mike | 2 |
| bob | 2 |
SELECT 4