{
    "version": "https:\/\/jsonfeed.org\/version\/1",
    "title": "LEFT JOIN: blog on analytics, visualisation & data science, posts tagged: Q&A",
    "home_page_url": "https:\/\/en.leftjoin.ru\/tags\/q-a\/",
    "feed_url": "https:\/\/en.leftjoin.ru\/tags\/q-a\/json\/",
    "icon": "https:\/\/en.leftjoin.ru\/user\/userpic@2x.jpg",
    "author": {
        "name": "Nikolay Valiotti",
        "url": "https:\/\/en.leftjoin.ru\/",
        "avatar": "https:\/\/en.leftjoin.ru\/user\/userpic@2x.jpg"
    },
    "items": [
        {
            "id": "50",
            "url": "https:\/\/en.leftjoin.ru\/all\/calculating-user-retention-by-24-hour-windows\/",
            "title": "Calculating User Retention by 24-hour windows and calendar days in SQL",
            "content_html": "<p>Yesterday I got a message from one of our blog readers, asking:<\/p>\n<blockquote>\n<p><i>Let’s say today’s Monday, and an app was downloaded 187 times. If I want to find out what’s the Retention rate was on day 1, what day of the week should I start taking the count with?<\/i><\/p>\n<\/blockquote>\n<p>He is referring to the blog post on <a href=\"https:\/\/en.leftjoin.ru\/all\/retention-rate\/\">Сalculating Retention Rate<\/a>. I would like to clarify that since retention rate can be calculated by 24-hour windows, as well as by calendar days. In our case, day 0 will be Monday, and day 1 will be Tuesday. However, there is one little hiccup...<\/p>\n<p>For example, if we started promoting our product on Monday, October 12 at 23:59, then all downloads of this day will have the retention rate of day 1. It’s a problem of performing calendar calculations. To address this, some data analysts calculate retention rate not only by calendar days but also by 24-hour windows.<\/p>\n<p>Let’s apply this idea to the above case:<\/p>\n<ul>\n<li>The Retention rate for day 0 can be calculated using the number of downloads from October 5, 23:59 to October 6, 23:59.<\/li>\n<li>The Retention rate for day 1:  from October 6, 23:59 to October 7, 23:59<\/li>\n<li>And so on rolling 24-hour window.<\/li>\n<\/ul>\n<h2>How to calculate Retention Rate by 24-hour windows in SQL?<\/h2>\n<p>Let’s recall one of the queries from our previous post. It was written to calculate the difference between the download date and the user activity date. We need to change the query so that user activity is calculated by 24-hour windows. To accomplish this just change the calculation for <i>datediff<\/i> to 24-hour windows, updating the lines in bold.<\/p>\n<pre class=\"e2-text-code\"><code class=\"SQL\">\r\nSELECT from_unixtime(user.installed_at, \"yyyy-MM-dd\") AS reg_date,\r\n   <b>floor((cast(cs.created_at as int)-cast(installed_at as int))\/(24*3600)) as date_diff,<\/b>\r\n   ndv(user.id) AS ret_base\r\n   FROM USER\r\n   LEFT JOIN client_session cs ON cs.user_id=user.id\r\n   WHERE 1=1\r\n     <b>AND floor((cast(cs.created_at as int)-cast(installed_at as int))\/(24*3600)) between 0 and 30<\/b>\r\n     AND from_unixtime(user.installed_at)>=date_add(now(), -60)\r\n     AND from_unixtime(user.installed_at)<=date_add(now(), -31)\r\n   GROUP BY 1,2\r\n<\/pre>\n<\/code><p>Updated query:<\/p>\n<pre class=\"e2-text-code\"><code>SELECT \r\n       cohort.date_diff AS day_difference,\r\n       avg(reg.users) AS cohort_size,\r\n       avg(cohort.ret_base) AS retention_base,\r\n       avg(cohort.ret_base)\/avg(reg.users)*100 AS retention_rate\r\nFROM\r\n  (SELECT from_unixtime(user.installed_at, &quot;yyyy-MM-dd&quot;) AS reg_date,\r\n          ndv(user.id) AS users\r\n   FROM USER\r\n   WHERE from_unixtime(user.installed_at)&gt;=date_add(now(), -60)\r\n     AND from_unixtime(user.installed_at)&lt;=date_add(now(), -31)\r\n   GROUP BY 1) reg\r\nLEFT JOIN\r\n  (SELECT from_unixtime(user.installed_at, &quot;yyyy-MM-dd&quot;) AS reg_date,\r\n    floor((cast(cs.created_at as int)-cast(installed_at as int))\/(24*3600)) as date_diff,\r\n          ndv(user.id) AS ret_base\r\n   FROM USER\r\n   LEFT JOIN client_session cs ON cs.user_id=user.id\r\n    WHERE 1=1\r\n     AND floor((cast(cs.created_at as int)-cast(installed_at as int))\/(24*3600)) between 0 and 30\r\n     AND from_unixtime(user.installed_at)&gt;=date_add(now(), -60)\r\n     AND from_unixtime(user.installed_at)&lt;=date_add(now(), -31)\r\n   GROUP BY 1,2\r\n  ) cohort ON reg.reg_date=cohort.reg_date\r\n    GROUP BY 1        \r\n    ORDER BY 1<\/code><\/pre><h2>Final output:<\/h2>\n<div class=\"e2-text-picture\">\n<img src=\"https:\/\/en.leftjoin.ru\/pictures\/1-19.png\" width=\"1170\" height=\"400\" alt=\"\" \/>\n<\/div>\n<p>Compare it with the previous one:<\/p>\n<div class=\"e2-text-picture\">\n<img src=\"https:\/\/en.leftjoin.ru\/pictures\/2-19.png\" width=\"1170\" height=\"400\" alt=\"\" \/>\n<\/div>\n<p>And as you see, the retention rate calculated using 24-hour windows is slightly lower in the first days.<\/p>\n",
            "date_published": "2020-10-21T15:44:39+03:00",
            "date_modified": "2020-10-21T16:15:27+03:00",
            "image": "https:\/\/en.leftjoin.ru\/pictures\/1-19.png",
            "_date_published_rfc2822": "Wed, 21 Oct 2020 15:44:39 +0300",
            "_rss_guid_is_permalink": "false",
            "_rss_guid": "50",
            "_e2_data": {
                "is_favourite": false,
                "links_required": [
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css"
                ],
                "og_images": [
                    "https:\/\/en.leftjoin.ru\/pictures\/1-19.png",
                    "https:\/\/en.leftjoin.ru\/pictures\/2-19.png"
                ]
            }
        }
    ],
    "_e2_version": 3386,
    "_e2_ua_string": "E2 (v3386; Aegea)"
}