Write a query to summarise rt_events for each calendar month containing events. Return month as the month’s first day, with type DATE; events as the row count; and active_users as the distinct user count.
Return events_per_user as events divided by active users, rounded to two decimal places. Do not add months with no events.
Ordering: Sort by month ascending.
Returnmontheventsactive_usersevents_per_user
About this problem
You practise the distinction the whole pattern rests on: an event is not a person. The query is four lines and the result puts both counts in the same row, so you can see that February had five events from one user. Every retention percentage later in this pattern has one of these two numbers in its numerator, and picking the wrong one is the lesson’s featured failure.
What makes it worth a problem of its own is how plausible the wrong answer looks. count(user_id) is the natural thing to write when you want a count of users – the column is named user_id, the function is named count, and the result is a number of about the right size. It counts rows. In this sample it turns February’s retention from one user into five, and because five is also the event count, the ratio column quietly becomes 1.00 for every month, which reads like a tidy dataset rather than a bug. The other half of the problem is where you put the counting. Pulling count(DISTINCT user_id) out into a subquery over the whole table gives 3 on every row, which is correct for the table and wrong for every month in it. Both mistakes produce a full result set with no NULLs and no errors, and the only way to notice is to check a month you can count by hand.
Read the lesson: Cohorts & Retention (P23) →