#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.
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.
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.
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.
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.
