learning-it by example

#124 SQL and analytics 6.6 min

Cohort Retention in 20 Lines of SQL

A 20-line SQL query builds a cohort retention table; remove DISTINCT and retention shows 150 percent.

1. How does the cohort retention query define a cohort?
2. In the cohort retention demo's made-up data, 10 users joined in January. How many were active one month later, and what was the retention?
3. Why can a cohort show higher retention in a later month than in an earlier month, as the January cohort did?
4. What happens when the word DISTINCT is removed from the active step of the cohort retention query, and why?
5. How does the cohort retention query work out each cohort's size for the percentage?

Practice only: nothing is saved and no score counts towards anything. Each answer comes with the reason.

  1. Run the three modes

    Copy the code folder first. Run python run.py events, python run.py retention and python run.py wrong. In the wrong output, find every row where pct is above 100 and write them down. Then say in one sentence what the wrong query is counting.

    A hint

    The wrong query is the same text as the right one, with one word removed.

  2. Everyone comes back

    In events.py, KEEP holds the chance that a user is active 0, 1, 2 and 3 months after joining. Change it to [1.0, 1.0, 1.0, 1.0] and predict what python run.py retention prints in the pct column. On this machine every row that is shown is 100.0, and the cohorts still stop where the data ends in April.

    A hint

    A chance of 1.0 means the random check never skips a month.

  3. Extend the data by a month

    In events.py, LAST_MONTH is 4 because the data ends in April. Change it to 5 and predict which cohort rows gain a new month. Then run python run.py retention. On this machine, February gains a month 3 row and March gains a month 2 row. The numbers in the old rows may also change, because the random draws now run through more months.

    A hint

    A cohort row exists for each month offset up to the end of the data.

  4. Break the query yourself

    In retention.py, the wrong query is made by replacing SELECT DISTINCT with SELECT. Instead of using that, edit the RETENTION text by hand and change the pct expression so it reads ROUND(100.0 * COUNT(*) / 10, 1) AS pct, deleting both FIRST_VALUE(COUNT(*)) and the OVER (PARTITION BY cohort ORDER BY k) line under it. Predict what happens to the February and March rows, then run python run.py retention to check. This is a prediction first: write your guess down before you run it.

    A hint

    February has 8 users and March has 12, so a fixed 10 gives them the wrong percentage.

These tasks are for you to try on your own machine. Nothing is checked or saved.

What this does not show

Thirty made-up users with chances picked by hand, checked only in SQLite, where active means any event.

Up next in SQL and analytics

More in SQL and analytics

All SQL and analytics videos

Related videos