<div>
Can you take the first 20 records for example and show the results&nbsp;you would expect?<br><br>
</div>


<div>
I ran this with the first 19 records and tested for customers acquired in January, 2017 and only showed three months, but you can expand it to meet your requirements -<br>with cte as<br>(select trunc(registrationdt,'mm') registrationdt, lastactivedt, ceil(months_between(lastactivedt, last_day(registrationdt))) monthsactive,<br>&nbsp;count(*) over (order by 1) tot<br>&nbsp;from yourtable<br>&nbsp;where trunc(registrationdt,'mm') = to_date('201701','YYYYMM')),--Select the date of the month to check<br>cte2 as<br>(select trunc(registrationdt,'mm') month,<br>&nbsp;sum(case when monthsactive &gt;= 1 then 1 else 0 end) as month1,<br>&nbsp;sum(case when monthsactive &gt;= 2 then 1 else 0 end) as month2,<br>&nbsp;sum(case when monthsactive &gt;= 3 then 1 else 0 end) as month3<br>&nbsp;from cte<br>&nbsp;group by trunc(registrationdt,'mm')<br>&nbsp;order by trunc(registrationdt,'mm'))<br>select distinct cte2.month,<br>to_char(round(cte2.month1/cte.tot,2)*100)||'%' month1pct,<br>to_char(round(cte2.month2/cte.tot,2)*100)||'%' month2pct,<br>to_char(round(cte2.month3/cte.tot,2)*100)||'%' month3pct<br>from cte inner join cte2<br>on trunc(cte.registrationdt,'mm') = cte2.month;<br><br>
</div>


<div>
Thanks,&nbsp;<a href="https://www.experts-exchange.com/members/awking00.html" rel="ugc">awking00</a>. &nbsp;I really appreciate your help. &nbsp;Can you please help add record count to the resultset to show the number of records in the cohort?
</div>


<div>
My example produced the following result fro customers acquired in January, 2017:<br><pre><code class="code-1 nolinks" id="code-20-43121433-1"><span>MONTH &nbsp; &nbsp; MONTH1P MONTH2P MONTH3P</span>
<span>--------- ------- ------- -------</span>
<span>01-JAN-17 100% &nbsp; &nbsp;100% &nbsp; &nbsp;40%</span>
</code></pre>There were five records that were acquired in January, 2017. So, would you like to see something like this?<br><pre><code class="code-1 nolinks" id="code-20-43121433-2"><span>MONTH &nbsp; &nbsp; MONTH1P MONTH2P MONTH3P&nbsp;&nbsp;&nbsp;COHORTS</span>
<span>--------- ------- ------- -------&nbsp;&nbsp;&nbsp;-------</span>
<span>01-JAN-17 100% &nbsp; &nbsp;100% &nbsp; &nbsp;40%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5</span>
</code></pre><br>
</div>


<div>
If that is what you want change the final query removing the distinct keyword, adding a count(*) and group by statements -<br>select cte2.month, -- <strong>Distinct keyword removed</strong><br>to_char(round(cte2.month1/cte.tot,2)*100)||'%' month1pct,<br>to_char(round(cte2.month2/cte.tot,2)*100)||'%' month2pct,<br>to_char(round(cte2.month3/cte.tot,2)*100)||'%' month3pct,<br><strong>count(*) cohorts</strong><br>from cte inner join cte2<br>on trunc(cte.registrationdt,'mm') = cte2.month<br><strong>group by cte2.month,<br>to_char(round(cte2.month1/cte.tot,2)*100)||'%',<br>to_char(round(cte2.month2/cte.tot,2)*100)||'%',<br>to_char(round(cte2.month3/cte.tot,2)*100)||'%';<br></strong>
</div>


<div>
Thanks again, <a href="https://www.experts-exchange.com/members/awking00.html" rel="ugc">awking00</a> . The result should look like this for 201701 and 201702 cohorts<br><br>&nbsp;<table><tbody><tr><td>Cohort</td><td>Count</td><td>Month1</td><td>Month2</td><td>Month3</td><td>Month4</td><td>Month5</td><td>Month6</td><td>Month7</td><td>Month8</td><td>Month9</td><td>Month10</td><td>Month11</td></tr><tr><td>201701</td><td>422</td><td>100%</td><td>91%</td><td>84%</td><td>76%</td><td>69%</td><td>61%</td><td>54%</td><td>45%</td><td>39%</td><td>29%</td><td>21%</td></tr><tr><td>201702</td><td>342</td><td>100%</td><td>94%</td><td>88%</td><td>80%</td><td>74%</td><td>65%</td><td>57%</td><td>49%</td><td>40%</td><td>31%</td><td>23%</td></tr></tbody></table><br>and the raw numbers should look like this for the number of customers who are still active each month from the original cohort<br>&nbsp;<table><tbody><tr><td>Cohort</td><td>Count</td><td>Month1</td><td>Month2</td><td>Month3</td><td>Month4</td><td>Month5</td><td>Month6</td><td>Month7</td><td>Month8</td><td>Month9</td><td>Month10</td><td>Month11</td></tr><tr><td>201701</td><td>422</td><td>420</td><td>386</td><td>353</td><td>320</td><td>293</td><td>259</td><td>228</td><td>192</td><td>164</td><td>124</td><td>90</td></tr><tr><td>201702</td><td>342</td><td>342</td><td>322</td><td>301</td><td>274</td><td>252</td><td>224</td><td>196</td><td>166</td><td>138</td><td>105</td><td>78</td></tr></tbody></table>
</div>


fb1990

<div>
To get headings exactly as you wanted -<br>to get raw numbers<br><pre><code class="code-10 nolinks" id="code-20-43121478-1"><span>with cte as</span>
<span>(select trunc(registrationdt,'mm') registrationdt, count(*) over (partition by trunc(registrationdt,'mm') order by 1) tot, lastactivedt,</span>
<span>&nbsp;ceil(months_between(lastactivedt, last_day(registrationdt))) monthsactive</span>
<span>&nbsp;from yourtable</span>
<span>&nbsp;where trunc(registrationdt,'mm') in (to_date('201701','YYYYMM'),to_date('201707','YYYYMM')))</span>
<span>select to_char(registrationdt,'YYYYMM') cohort, tot as count,</span>
<span>&nbsp;sum(case when monthsactive &gt;= 1 then 1 else 0 end) as month1,</span>
<span>&nbsp;sum(case when monthsactive &gt;= 2 then 1 else 0 end) as month2,</span>
<span>&nbsp;sum(case when monthsactive &gt;= 3 then 1 else 0 end) as month3,</span>
<span>&nbsp;sum(case when monthsactive &gt;= 4 then 1 else 0 end) as month4,</span>
<span>&nbsp;sum(case when monthsactive &gt;= 5 then 1 else 0 end) as month5,</span>
<span>&nbsp;sum(case when monthsactive &gt;= 6 then 1 else 0 end) as month6,</span>
<span>&nbsp;sum(case when monthsactive &gt;= 7 then 1 else 0 end) as month7,</span>
<span>&nbsp;sum(case when monthsactive &gt;= 8 then 1 else 0 end) as month8,</span>
<span>&nbsp;sum(case when monthsactive &gt;= 9 then 1 else 0 end) as month9,</span>
<span>&nbsp;sum(case when monthsactive &gt;= 10 then 1 else 0 end) as month10,</span>
<span>&nbsp;sum(case when monthsactive &gt;= 11 then 1 else 0 end) as month11</span>
<span>&nbsp;from cte</span>
<span>&nbsp;group by to_char(registrationdt,'YYYYMM'),tot</span>
<span>&nbsp;order by to_char(registrationdt,'YYYYMM'),tot;</span>
</code></pre>to get percentages<pre><code class="code-10 nolinks" id="code-20-43121478-2"><span>with cte as</span>
<span>(select trunc(registrationdt,'mm') registrationdt, lastactivedt, ceil(months_between(lastactivedt, last_day(registrationdt))) monthsactive,</span>
<span>&nbsp;count(*) over (order by 1) tot</span>
<span>&nbsp;from yourtable</span>
<span>&nbsp;where trunc(registrationdt,'mm') in (to_date('201701','YYYYMM'),to_date('201707','YYYYMM'))),</span>
<span>cte2 as</span>
<span>(select trunc(registrationdt,'mm') month,</span>
<span>&nbsp;sum(case when monthsactive &gt;= 1 then 1 else 0 end) as month1,</span>
<span>&nbsp;sum(case when monthsactive &gt;= 2 then 1 else 0 end) as month2,</span>
<span>&nbsp;sum(case when monthsactive &gt;= 3 then 1 else 0 end) as month3,</span>
<span>&nbsp;sum(case when monthsactive &gt;= 4 then 1 else 0 end) as month4,</span>
<span>&nbsp;sum(case when monthsactive &gt;= 5 then 1 else 0 end) as month5,</span>
<span>&nbsp;sum(case when monthsactive &gt;= 6 then 1 else 0 end) as month6,</span>
<span>&nbsp;sum(case when monthsactive &gt;= 7 then 1 else 0 end) as month7,</span>
<span>&nbsp;sum(case when monthsactive &gt;= 8 then 1 else 0 end) as month8,</span>
<span>&nbsp;sum(case when monthsactive &gt;= 9 then 1 else 0 end) as month9,</span>
<span>&nbsp;sum(case when monthsactive &gt;= 10 then 1 else 0 end) as month10,</span>
<span>&nbsp;sum(case when monthsactive &gt;= 11 then 1 else 0 end) as month11</span>
<span>&nbsp;from cte</span>
<span>&nbsp;group by trunc(registrationdt,'mm')</span>
<span>&nbsp;order by trunc(registrationdt,'mm'))</span>
<span>select to_char(cte2.month,'YYYYMM') cohort,count(*) count,</span>
<span>to_char(round(cte2.month1/cte.tot,2)*100)||'%' month1pct,</span>
<span>to_char(round(cte2.month2/cte.tot,2)*100)||'%' month2pct,</span>
<span>to_char(round(cte2.month3/cte.tot,2)*100)||'%' month3pct,</span>
<span>to_char(round(cte2.month4/cte.tot,2)*100)||'%' month4pct,</span>
<span>to_char(round(cte2.month5/cte.tot,2)*100)||'%' month5pct,</span>
<span>to_char(round(cte2.month6/cte.tot,2)*100)||'%' month6pct,</span>
<span>to_char(round(cte2.month7/cte.tot,2)*100)||'%' month7pct,</span>
<span>to_char(round(cte2.month8/cte.tot,2)*100)||'%' month8pct,</span>
<span>to_char(round(cte2.month9/cte.tot,2)*100)||'%' month9pct,</span>
<span>to_char(round(cte2.month10/cte.tot,2)*100)||'%' month10pct,</span>
<span>to_char(round(cte2.month11/cte.tot,2)*100)||'%' month11pct</span>
<span>from cte inner join cte2</span>
<span>on trunc(cte.registrationdt,'mm') = cte2.month</span>
<span>group by cte2.month,</span>
<span>to_char(round(cte2.month1/cte.tot,2)*100)||'%',</span>
<span>to_char(round(cte2.month2/cte.tot,2)*100)||'%',</span>
<span>to_char(round(cte2.month3/cte.tot,2)*100)||'%',</span>
<span>to_char(round(cte2.month4/cte.tot,2)*100)||'%',</span>
<span>to_char(round(cte2.month5/cte.tot,2)*100)||'%',</span>
<span>to_char(round(cte2.month6/cte.tot,2)*100)||'%',</span>
<span>to_char(round(cte2.month7/cte.tot,2)*100)||'%',</span>
<span>to_char(round(cte2.month8/cte.tot,2)*100)||'%',</span>
<span>to_char(round(cte2.month9/cte.tot,2)*100)||'%',</span>
<span>to_char(round(cte2.month10/cte.tot,2)*100)||'%',</span>
<span>to_char(round(cte2.month11/cte.tot,2)*100)||'%';</span>
<span></span>
</code></pre>
</div>


<div>
Hello&nbsp;<a href="https://www.experts-exchange.com/members/awking00.html" rel="ugc">awking00</a>, Can check this part of the code for me:<br><br>count(*) over (partition by trunc(registrationdt,'mm') order by 1)<br>and &nbsp;<br>count(*) over (order by 1) tot <br><br>The numbers are higher than the ones displayed in the example cohort that I gave above.<br><br>Thanks for all your help
</div>


<div>
Sorry about that. My first attempt took the position that you would only be entering one month to check. Try changing that to count(*) over (partition by trunc(registrationdt,'mm') order by 1) tot.
</div>


<div>
Hello&nbsp;&nbsp;<a href="https://www.experts-exchange.com/members/awking00.html" rel="ugc">awking00</a>, <br><br>Yes, I am checking one month at a time. But the numbers are still high thereby creating lower active % in outer periods.&nbsp;
</div>


<div>
I may be wrong about changing that count statement. Can you send me insert statements for the cohorts that I can use to test to see if we're coming up with the same results. I don't know why the active percentages would be lower and need to look at actual data.
</div>


<div>
I know that was a fair amount of effort, but what I would really like to see is the inserts in a text file that I could simply copy and paste in sqlplus (the code snippet shows <strong>5:Insert ...</strong>, for example so I can't copy and paste from there). Also the inserts as you have created them show errors. There's no apostrophe after the registrationdt plus the inserts need to include the to_date function. The following format works for me:<br>insert into test_tab values (to_date('1/1/2017','mm/dd/yyyy'), to_date('3/22/2017','mm/dd/yyyy'));<br>Note - the attribute list after test_tab is optional since the table only has the two fields.
</div>


<div>
Ok, thank you. Do you still want me to send the inserts?&nbsp; Thanks again for your help.<br><br>
</div>


<div>
Sorry I couldn't get back to you sooner. I've been out all afternoon for a medical appointment. I would still like to have those inserts to see if I get the expected results or if I also get higher numbers and, subsequently, lower percentages.<br><br>
</div>


<div>
Thank you so much. My hope is, you are well. &nbsp;Here is the sample insert<br><br><a href="https://filedb.experts-exchange.com/incoming/2020/07_w29/1477613/sample-cohort-201701.txt" rel="ugc">sample cohort 201701.txt</a>
</div>


sample-cohort-201701.txt

<div>
Running the query with the data provided from your inserts returns the following results:<br>For the raw numbers -<pre><code class="code-1 nolinks" id="code-20-43122452-1"><span>COHORT &nbsp; &nbsp; &nbsp;COUNT MONTH1 MONTH2 MONTH3 MONTH4 MONTH5 MONTH6 MONTH7 MONTH8 MONTH9 MONTH10 MONTH11</span>
<span>------ ---------- ------ ------ ------ ------ ------ ------ ------ ------ ------ ------- -------</span>
<span>201701 &nbsp; &nbsp; &nbsp; &nbsp;401 &nbsp; &nbsp;387 &nbsp; &nbsp;348 &nbsp; &nbsp;314 &nbsp; &nbsp;288 &nbsp; &nbsp;264 &nbsp; &nbsp;229 &nbsp; &nbsp;195 &nbsp; &nbsp;168 &nbsp; &nbsp;137 &nbsp; &nbsp; &nbsp;98 &nbsp; &nbsp; &nbsp;71</span>
</code></pre>For the percentages -<br><pre><code class="code-1 nolinks" id="code-20-43122452-2"><span>COHORT &nbsp; &nbsp; &nbsp;COUNT MONT MONT MONT MONT MONT MONT MONT MONT MONT MONT MONT</span>
<span>------ ---------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----</span>
<span>201701 &nbsp; &nbsp; &nbsp; &nbsp;401 97% &nbsp;87% &nbsp;78% &nbsp;72% &nbsp;66% &nbsp;57% &nbsp;49% &nbsp;42% &nbsp;34% &nbsp;24% &nbsp;18%</span>
</code></pre>It seems the only thing that differs from what you indicated earlier for results comes from the total number of records where you showed 422 but there were only 401 inserts.
</div>


<div>
Yes, this is better. &nbsp;Can you please send me the code? &nbsp;WOW<br><br>Or did use the code you already posted.
</div>


<div>
Thank you so much for all your help.&nbsp; I really appreciate you. Thanks.
</div>


<div>
This was an intriguing problem. Glad I could help :-)
</div>


<div>
<div class="content wysiwyg-content">
Hello EE,<br />
<br />
Can someone please help me with a SQL code to generate a cohort analysis from the Oracle database? &nbsp;I have attached a sample data. &nbsp;I want to see calculate what % of the customers acquired in a specified month are still active in Month1, Month2, Month3, etc. The goal is to take the data to excel for final rollup and visualization, but I need the code to model the data<br />
<a href="https://filedb.experts-exchange.com/incoming/2020/07_w28/1476621/Cohort-Dataset.xlsx" target="_blank" class="file-inline" title="Sample (89 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Cohort-Dataset.xlsx</a>
</div>
</div>


Cohort-Dataset.xlsx

Hello EE,

Can someone please help me with a SQL code to generate a cohort analysis from the Oracle database?  I have attached a sample data.  I want to see calculate what % of the customers acquired in a specified month are still active in Month1, Month2, Month3, etc. The goal is to take the data to excel for final rollup and visualization, but I need the code to model the data

Cohort Analysis

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

Oracle Database

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.