SQL: Calculating the day over day repeat user percentage

Question

I have an events table that has an user_id and created_at columns.

What I'd like to figure out is the percentage of users having day-over-day repeat entries in the events table.

So, if day d1 has user_ids [a,b,c], and day d2 has user_ids [b,d,e], then b is the only repeat user_id of the three (from d1), and there is 33% overlap between d1 and d2.

I'd like to be able to extend this for an arbitrary number of days.

The schema in question:

CREATE TABLE events (
  events_id serial PRIMARY KEY
, user_id VARCHAR(255) NOT NULL
, created_at datetime NOT NULL
);

This is a large table, having ~25MM rows per day, with ~4.5MM distinct user_ids per day.

Example data set:

+---------+---------------------+
| user_id |     created_at      |
+---------+---------------------+
| bob     | 2014-12-02 11:11:11 |
| sally   | 2014-12-02 12:12:11 |
| zed     | 2014-12-02 12:22:11 |
|         | ...                 |
| chris   | 2014-12-03 11:13:11 |
| mark    | 2014-12-03 11:11:13 |
| zed     | 2014-12-03 11:11:33 |
|         | ...                 |
| sydney  | 2014-12-04 11:14:11 |
| zed     | 2014-12-04 11:44:11 |
| chris   | 2014-12-04 11:44:11 |
|         | ...                 |
| sydney  | 2014-12-05 11:15:11 |
| zed     | 2014-12-05 11:55:11 |
| chris   | 2014-12-05 11:55:15 |
| sandy   | 2014-12-05 11:55:51 |
| sydney  | 2014-12-05 11:55:55 |
+---------+---------------------+

Expected output:

+------------+---------------------------+
|    day     | returning_user_percentage |
+------------+---------------------------+
| 2014-12-02 | NULL                      |
| 2014-12-03 | 33                        |
| 2014-12-04 | 66                        |
| 2014-12-05 | 75                        |
+------------+---------------------------+

Additionally, and quite probably far more simple, part 2: I'd like to know how many new users there are each day, where "new" means the user_id has not previously been seen.

Answer 1

Assuming that created_at is a date with no time component:

select e.created_at,
       avg(case when eprev.user_id is not null then 1.0 else 0.0 end) as overlap
from events e left join
     events eprev
     on e.created_at = eprev.created_at + interval '1' day and e.user_id = eprev.user_id
group by e.created_at

Answer 2

Answer to updated question:

"Calculate for every day the percentage of distinct users that have entries for the previous day as well."

WITH e AS (SELECT created_at::date, user_id AS day FROM events GROUP BY 1, 2)
SELECT e.day
     , round(100.0 * count(e1.user_id) / count(*), 2) AS pct_repeat_user
FROM   e
LEFT   JOIN e e1 ON e1.user_id = e.user_id
                AND e1.day = e.day - 1
GROUP  BY 1
ORDER  BY 1;

Returns your desired result - but 0 instead of NULL for the first day, which seems more correct to me.

Explain

• Your "date" is actually a timestamp (not "datetime"). To get unique users per day, I cast to date and group users in CTE u. This is crucial or you get nonsensical results from cross-joining duplicate users on one day with duplicate users on the previous day.

• Building on that, LEFT JOIN to the previous day. Count users that can be found "yesterday" as well and divide by the number of users "today". Multiply by 100.0 to coerce the number to numeric before dividing and use round() to get your desired number of fractional digits.

• Since day is data type date now you can simply subtract an integer 1 from it to get "yesterday.

• Be aware that a "day" is also defined by the time zone - if you should have data from multiple time zones. Does not act up as long as you just store timestamp, but there may be an inherent error in your data. Details:

  • Ignoring timezones altogether in Rails and PostgreSQL

SQL Fiddle.

DB design

For millions of rows and multiple entries per user I would urgently advice to create a separate users table and reference it to reduce disk space and improve performance:

CREATE TABLE users (
  user_id serial PRIMARY KEY
, username text NOT NULL
);

CREATE TABLE events (
  events_id serial PRIMARY KEY
, user_id int NOT NULL REFERENCES users
, created_at timestamp NOT NULL
);

This would also instrumental to speed up a number or other queries.