learning-it by example

Back to the video

Cohort Retention in 20 Lines of SQL

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

Video #124 ยท SQL and analytics

Steps in the video

  1. 0:00 Twenty lines of SQL. Remove one word and retention hits 150 percent.
  2. 0:25 The idea
  3. 0:56 Setup
  4. 1:21 Thirty made-up users
  5. 1:52 One row per event
  6. 2:19 Three small steps
  7. 2:44 Count and divide
  8. 3:09 The run
  9. 4:39 What this does not show
  10. 5:04 Mistakes to avoid
  11. 5:36 My take and next steps
  12. 6:03 Recap

Files

  • events.py (31 lines)
  • retention.py (23 lines)
  • run.py (24 lines)

Download the code (zip)

Tested with

Python 3.12.15

What this does not show

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

Sources

Learning IT by Example.