CODEWITHSUNDEEP
sqlsqlite
James W.
James W.

Reputation: 3055

sql calculate retention

how do I calculate retention rate in SQL to add as a column to my table

  1. first day I had in Brazil 411 unique users
  2. second day only 154 users came back (retention rate is 154/411)
  3. third day only 115 came back again (115/154)

  4. last day only 81 out of 98 users came back

     Country      Date  sum_views  distinct_user_ids
   Brazil  5/5/2016              3793           411
   Brazil  5/6/2016              1632           154
   Brazil  5/7/2016              1456           115
   Brazil  5/8/2016              1223            98
   Brazil  5/9/2016               993            81
   Canada  5/5/2016              6419           708
   Canada  5/6/2016              2649           235
   Canada  5/7/2016              2578           197
   Canada  5/8/2016              2024           151
   Canada  5/9/2016              1893           141
   United states  5/5/2016             13007          1438
   United states  5/6/2016              5755           522
   United states  5/7/2016              5502           419
   United states  5/8/2016              4915           362
   United states  5/9/2016              3713           284

output :

a column with retention rate

Upvotes: 0

Views: 1798

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You need the previous value for the denominator. You can get that with a correlated subquery:

select t.*, tprev.distinct_user_ids,
       (t.distinct_user_ids / tprev.distinct_user_ids)
from t join
     t tprev
     on tprev.country = t.country and
        tprev.date = date(t.date, '-1 day');

Upvotes: 2

Related Questions