How to divide the value of a column by another (the divisor is always a predefined and fixed row)?-CodePudding

So basically i have a table like this, with the year, month, and number of clients (called cohort). The column is called cohort because in jan 2015 i counted all my unique buyers and now i'm checking how many are returning to buy more products in the following months.

 ---------------- ----------- 
|  YEAR  | MONTH |  COHORT   |
 ---------------- ----------- 
|  2015  |  01   |  100      |
|  2015  |  02   |  54       |
|  2015  |  03   |  32       |
|  2015  |  04   |  29       |
|  2015  |  05   |  26       |
|  2015  |  06   |  17       |
|  2015  |  07   |  09       |
|  2015  |  08   |  03       |
 -------- ------- -----------

However, i need to know what's the percentage of returning clients from feb 2015 onwards. So i need something like this:

 ---------------- ----------- ---------- 
|  YEAR  | MONTH |  COHORT   |  SHARE   |
 ---------------- ----------- ---------- 
|  2015  |  01   |  212      |  100%    |
|  2015  |  02   |  54       |  25%     |
|  2015  |  03   |  32       |  15%     |
|  2015  |  04   |  29       |  13%     |
|  2015  |  05   |  26       |  12%     |
|  2015  |  06   |  17       |  8%      |
|  2015  |  07   |  09       |  4%      |
|  2015  |  08   |  03       |  1%      |
 -------- ------- ----------- ----------

How can I do that knowing that the minimum year and month is my basis/total cohort?

CodePudding user response：

Consider below

select *, 
  round(100 * COHORT / (first_value(COHORT) over(order by YEAR, MONTH)), 2) SHARE
from your_table
where YEAR >= 2015 and MONTH >= 1

if applied to sample data in your question - output is

CodePudding user response：

You could use a scalar subquery to find the size of the January 2015 cohort. Then, normalize each subsequent cohort using this value to find the percentage.

SELECT YEAR, MONTH, COHORT,
       100.0 * COHORT / (SELECT COHORT FROM yourTable
                         WHERE YEAR = 2015 AND MONTH = 1) AS SHARE
FROM yourTable
ORDER BY YEAR, MONTH;