Sql-server – Window Function to get pairs of records and compute the time difference

Here is another option. It still doesn't use your CONNECT BY (probably a good thing), but does have fewer table scans. For the SQL Fiddle I used Phil's modified data.

SELECT usr, usrmax - (slice-1)*7 WindowEnd, count(*) FROM 
(
  SELECT usr, max(dt) OVER (PARTITION BY Usr) usrmax
     , TRUNC((max(dt) OVER (PARTITION BY Usr) - dt)/7) + 1 Slice
  FROM Sales s
) a
GROUP BY usr, usrmax, slice
ORDER BY usr, WindowEnd;

⁺¹ For an excellent and interesting question.

Assuming this sanitized table definition

CREATE TABLE events (
  event_id   serial PRIMARY KEY
, user_id    int
, event_type int
, ts         timestamp  -- don't use reserved word as identifier
);

Your comparison seems unfair, the first query has ORDER BY event_id, but the second hasn't. The EXPLAIN output does not fit the first query (no sort step). Be sure to run all tests with the same ORDER BY clause to get valid results. Best run a couple of times and compare the best of 5 to eliminate caching effects.

Index

The key to performance is this multicolumn index:

CREATE INDEX events_fast_idx ON events (user_id, event_type, ts);

The sequence of columns matters! Why?

• Multicolumn index and performance

Queries

Each of your queries can be improved:

Query 1

Remove group by event_type, user_id without substitution:

SELECT event_id, user_id, event_type, ts
    , (SELECT count(*) 
       FROM   events 
       WHERE  user_id    = e.user_id 
       AND    event_type = e.event_type
       AND    ts >= e.ts - interval '30 days'
       AND    ts <= e.ts
      ) AS  ct
FROM   events e
ORDER  BY event_id;

Equivalent with more modern LATERAL join (Postgres 9.3+):

SELECT *
FROM   events e
    ,  LATERAL (
   SELECT count(*) AS ct
   FROM   events 
   WHERE  user_id    = e.user_id 
   AND    event_type = e.event_type
   AND    ts >= e.ts - interval '30 days'
   AND    ts <= e.ts
   ) ct
ORDER  BY event_id;

Which might also be the fastest query in combination with above index.
Related answer with more explanation:

• Optimize GROUP BY query to retrieve latest record per user

Query 2

• last_value(ts) OVER w as lv is just an expensive copy of ts.
• ROWS UNBOUNDED PRECEDING is the default and hence just noise.

SELECT event_id, user_id, event_type, ts, count(*) AS ct
FROM  (
   SELECT event_id, user_id, event_type, ts
        , unnest(array_agg(ts) OVER (PARTITION BY user_id, event_type
                                     ORDER BY ts)) AS agg
   FROM   events   
   ) e
WHERE  agg >= ts - interval '30 days'
GROUP  BY event_id, user_id, event_type, ts
ORDER  BY event_id;

But this is needlessly complex. The same logic can be had much cheaper with a join instead of the subquery with window function:

SELECT e.*, count(*) AS ct
FROM   events e
JOIN   events x USING (user_id, event_type)
WHERE  x.ts >= e.ts - interval '30 days'
AND    x.ts <= e.ts
GROUP  BY e.event_id
ORDER  BY e.event_id;

Which is my other favorite for top performance. Again with the above index.

Other query

Here is another idea, but I doubt it can compete. Give it a go, though:

WITH cte AS (
   SELECT event_id, user_id, event_type, ts
        , row_number(*) OVER (PARTITION BY user_id, event_type
                              ORDER BY ts) AS rn
   FROM   events
   )
SELECT e.event_id, e.user_id, e.event_type, e.ts, e.rn - min(x.rn) + 1 AS ct
FROM   cte e
JOIN   cte x USING (user_id, event_type)
WHERE  x.ts >= e.ts - interval '30 days'
AND    x.ts <= e.ts
GROUP  BY e.event_id, e.user_id, e.event_type, e.ts, e.rn
ORDER  BY e.event_id;

SQL Fiddle demonstrating all in Postgres 9.3.