(1, 1, 12, 0.099999),

(2, 1, 13, 0.105),

(3, 1, 14, 0.102),

(4, 1, 15, 0.1),

(5, 1, 22, 0.12),

(6, 1, 23, 0.101),

(7, 1, 24, 0.11),

(8, 2, 13, 0.105),

(9, 2, 14, 0.102),

(10, 2, 15, 0.1),

(11, 2, 16, 0.11),

(12, 2, 17, 0.11),

(13, 2, 18, 0.0995),

(14, 2, 19, 0.0998),

(15, 1, 12, 0.099999),

(16, 1, 13, 0.105),

(17, 1, 23, 0.101),

(18, 1, 24, 0.11),

(19, 2, 15, 0.1),

(20, 2, 16, 0.11),

(21, 2, 17, 0.11),

(22, 2, 18, 0.0995),

(23, 2, 19, 0.0998),

(24, 1, 13, 0.105),

(25, 1, 15, 0.1),

(26, 1, 22, 0.12),

(27, 1, 23, 0.101),

(28, 1, 24, 0.11),

(29, 2, 13, 0.105),

(30, 2, 14, 0.102),

(31, 2, 15, 0.1),

(32, 2, 16, 0.11),

(33, 2, 19, 0.0998);

-- Get only 1 tid / pid pair

pidtid AS (SELECT DISTINCT pid, tid FROM distinct_example),

-- Reconstitute single tids

tid AS (SELECT DISTINCT tid, duration FROM distinct_example),

-- Now sum up pid by pid

sum AS

(

SELECT pid, COUNT(1) num_times, SUM(duration) exposure_time

FROM tid JOIN pidtid USING (tid)

GROUP BY pid

),

-- Start another "thread" for visits (another = we do not SELECT from the previous CTEs, we start straight from distinct_example)

-- The following CTE is optional, it recomputes ids *if they are not sequential*.

-- (some RDBMS allow choosing the same name as the original table to transparently override any use of the original table in the subsequent SELECTs)

distexreindexed AS (select row_number() OVER (ORDER BY id) id, pid, tid, duration FROM distinct_example),

prev AS

(

SELECT

id, pid, tid,

CASE WHEN id <= 6 + LAG(id) OVER (PARTITION BY pid ORDER BY id) THEN 0 ELSE 1 END AS new_visit

FROM distexreindexed -- ← Switch to the reindexed table.

),

visits AS (SELECT pid, SUM(new_visit) visits FROM prev GROUP BY pid)

SELECT sum.*, visits FROM sum JOIN visits USING (pid) ORDER BY pid;