for-old-times-sake.sql

-- set up some test data
create table t2 (event text not null, ts timestamp not null);
insert into t2 (event, ts) select case when random() < 0.5 then 'event1' else 'event2' end, now() + (random() * 60 * '1 minute'::interval)  from generate_series(1, 100);

-- earliest_time
select min(ts)
  from t2
 where event = 'event1'
  group by event;
  
-- latest_time
select max(ts)
  from t2
 where event = 'event1'
  group by event;

-- last
SELECT *
FROM (
    SELECT event, 
           ts,
           ROW_NUMBER() OVER (PARTITION BY event ORDER BY ts DESC) AS rn
    FROM t2        
) subquery
WHERE rn = 1;

-- rate
WITH event_totals AS (
    SELECT
        date_trunc('minute', ts) AS time_interval,
        event,
        count(event) AS total_events
    FROM t2
    GROUP BY time_interval, event
    ORDER BY time_interval, event
)
SELECT
    time_interval,
    event,
    total_events,
    COALESCE(
        (total_events - LAG(total_events, 1) OVER (ORDER BY event, time_interval)) / 
        NULLIF(EXTRACT(epoch FROM (time_interval - LAG(time_interval, 1) OVER (ORDER BY event, time_interval))), 0),
    0) AS event_rate
FROM event_totals;