Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE yammer_events (
occurred_at TIMESTAMP,
user_id INT,
event_name VARCHAR(50)
);

INSERT INTO yammer_events (occurred_at, user_id, event_name) VALUES 
('2014-06-01 00:00:01', 1, 'search_autocomplete'),
('2014-06-01 00:00:01', 2,  'search_autocomplete'),
('2014-07-01 00:00:01', 1, 'search_run'),
('2014-07-01 00:00:02', 1, 'search_run'),
('2014-07-01 00:00:01', 2,  'search_run'),
('2014-07-01 00:00:01', 3,  'search_run'),
('2014-08-01 00:00:01', 1,  'search_run'),
('2014-08-01 00:00:01', 4,  'search_run');

CREATE TABLE



INSERT 0 8


WITH monthly_activity AS (
  SELECT distinct DATE_TRUNC('month', occurred_at) AS month,
  user_id
FROM yammer_events
WHERE event_name LIKE 'search%'
)

SELECT last_month.month+INTERVAL '1 month', COUNT(DISTINCT last_month.user_id)
FROM monthly_activity last_month
LEFT JOIN monthly_activity this_month
ON last_month.user_id = this_month.user_id
AND this_month.month = last_month.month + INTERVAL '1 month'
AND this_month.user_id IS NULL
GROUP BY 1 


?column?
count


2014-07-01 00:00:00
2

2014-08-01 00:00:00
3

2014-09-01 00:00:00
2



SELECT 3


WITH j AS (
  SELECT date_trunc('month',occurred_at::date) AS month,
    array_agg(distinct user_id) AS users,
    count(distinct user_id) AS total_users
  FROM yammer_events
  GROUP BY 1
  ORDER BY 1
)
SELECT month::date, 
  cardinality(LAG(users) OVER w - users) AS churn_count,
  (cardinality(LAG(users) OVER w - users)::numeric / 
    LAG(total_users) OVER w::numeric) * 100 AS churn_rate_percentage
FROM j
WINDOW w AS (ORDER BY month
             ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)


month
churn_count
churn_rate_percentage


2014-06-01
null
null

2014-07-01
0
0.00000000000000000000

2014-08-01
2
66.66666666666666666700



SELECT 3

?column?	count
2014-07-01 00:00:00	2
2014-08-01 00:00:00	3
2014-09-01 00:00:00	2

month	churn_count	churn_rate_percentage
2014-06-01	null	null
2014-07-01	0	0.00000000000000000000
2014-08-01	2	66.66666666666666666700