PGSQL finding if rows have other rows in a time range near them

explanation.md

I'm trying to find rows in a table where there have been other rows created within a minute of them.

Why you can't use group by:

You can't use group by for this because certain rows will exist inside of 2 groups.

 timestamp                      minute logical group
----------------------------------------------------------- 
2010-01-01 09:09:01             A1
2010-01-01 09:09:58             A1, A2
2010-01-01 09:10:02             B1, A2
2010-01-01 09:10:58             B1
2010-01-01 09:15:00             C1
2010-01-01 09:15:50             C1

solution.sql

-- this is not very performant... but it'll work
-- maybe this is what CTEs are for?
SELECT * FROM (
  SELECT id,
        created_at,
        (foo.created_at + (interval '1 minute')) AS one_minute_before,
        (foo.created_at - (interval '1 minute')) AS one_minute_after,
        (
          SELECT count(*) AS the_count
          FROM foo foo2
          WHERE foo2.created_at BETWEEN (foo.created_at - (interval '1 minute')) AND (foo.created_at + (interval '1 minute'))
          AND foo.id<>foo2.id
        )
  FROM foo
) AS some_inner_query
WHERE the_count>0;

table_setup.sql

CREATE TABLE foo ( id integer NOT NULL, created_at timestamp without time zone);
CREATE SEQUENCE foo_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
ALTER SEQUENCE foo_id_seq OWNED BY foo.id;
ALTER TABLE ONLY foo ADD CONSTRAINT assignments_pkey PRIMARY KEY (id);


INSERT INTO foo(id,created_at) VALUES(nextval('foo_id_seq'),'2010-01-01 09:09:01');
INSERT INTO foo(id,created_at) VALUES(nextval('foo_id_seq'),'2010-01-01 09:09:58');
INSERT INTO foo(id,created_at) VALUES(nextval('foo_id_seq'),'2010-01-01 09:10:02');
INSERT INTO foo(id,created_at) VALUES(nextval('foo_id_seq'),'2010-01-01 09:10:58');
INSERT INTO foo(id,created_at) VALUES(nextval('foo_id_seq'),'2010-01-01 09:15:00');
INSERT INTO foo(id,created_at) VALUES(nextval('foo_id_seq'),'2010-01-01 09:15:50');
INSERT INTO foo(id,created_at) VALUES(nextval('foo_id_seq'),'2010-01-01 09:20:50');