The goal is to create this UI: https://dl.dropboxusercontent.com/spa/quq37nq1583x0lf/sesbgnat.png

1-schema.sql

CREATE TABLE users (
  id             bigserial           PRIMARY KEY,
  uname          text                NOT NULL,
  created_at     timestamptz         NOT NULL DEFAULT NOW()
);

-- A hit is inserted for every authenticated page view
CREATE TABLE hits (
  id             bigserial           PRIMARY KEY,
  user_id        int                 NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  ip_address     inet                NOT NULL,
  -- track is a uuid kept in a cookie to detect people logging into multiple accounts
  -- on the same browser.
  track          uuid                NOT NULL,
  created_at     timestamptz         NOT NULL DEFAULT NOW()
);

CREATE INDEX ON hits (user_id);
CREATE INDEX ON hits (ip_root(ip_address));
CREATE INDEX ON hits (track);
CREATE INDEX ON hits (created_at);

2-find-users.sql

-- Find all users connected to an ip_address or track.
--
-- It finds all hits that match the current request's ip_address or track.
-- And then it recursively searches for hits linked to each found hit.
--
-- Marks each match as a 'TRACK' or 'IP_ADDRESS' and finds the most
-- recent match timestamp. For example, there's a big difference between
-- an ip address reused within 10 minutes vs 10 months. 

-- The goal is to create this UI: https://dl.dropboxusercontent.com/spa/quq37nq1583x0lf/sesbgnat.png

-- match        latest_match_at                 user
-- -------------------------------------------------------------------------------
-- TRACK        2017-06-24 17:20:41.009983-05   {"id":7,"uname":"banned", ...}
-- TRACK        2017-06-24 19:37:58.572795-05   {"id":5,"uname":"mod", ...}
-- IP_ADDRESS   2017-06-24 16:04:08.914108-05   {"id":1,"uname":"foo", ...}
-- IP_ADDRESS   2017-06-24 17:20:41.009983-05   {"id":7,"uname":"banned", ...}
-- IP_ADDRESS   2017-06-24 19:37:58.572795-05   {"id":5,"uname":"mod", ...}
-- TRACK        2017-06-24 16:05:35.440597-05   {"id":1,"uname":"foo","digest", ...}
-- IP_ADDRESS   2017-06-24 17:20:41.009983-05   {"id":3,"uname":"fuz", ...}

WITH RECURSIVE sub1 (user_id, ip_address, track, created_at, match) AS (
  SELECT
    user_id, ip_address, track, created_at,
    CASE
      WHEN hits.track = ${track} THEN 'TRACK'
      WHEN ip_root(hits.ip_address) = ip_root(${ipAddress}) THEN 'IP_ADDRESS'
    END as "match"
  FROM hits
  WHERE track = ${track}
     OR ip_root(ip_address) = ip_root(${ipAddress})

  UNION

  SELECT
    hits.user_id, hits.ip_address, hits.track, hits.created_at,
    CASE
      WHEN hits.track = sub1.track THEN 'TRACK'
      WHEN ip_root(hits.ip_address) = ip_root(sub1.ip_address) THEN 'IP_ADDRESS'
    END as "match"
  FROM sub1, hits
  WHERE hits.user_id != sub1.user_id
    AND (
      hits.track = sub1.track
      OR ip_root(hits.ip_address) = ip_root(sub1.ip_address)
    )
)

SELECT
  sub1.match,
  MAX(sub1.created_at) "latest_match_at",
  (SELECT to_json(users.*) FROM users WHERE id = sub1.user_id) "user"
FROM sub1
GROUP BY sub1.user_id, sub1.match