How to compare the current value of COUNT(*) with the previous one?

Question

SQL

Hello!
For example, there is a table:

+----+----------+----------------------------+
| id | nickname | created_at                 |
+----+----------+----------------------------+
| 1  | user_1   | 2009-07-30 21:51:51.829000 |
+----+----------+----------------------------+
| 2  | user_2   | 2009-08-27 21:52:21.016000 |
+----+----------+----------------------------+
| 3  | user_3   | 2009-09-30 21:52:00.417000 |
+----+----------+----------------------------+
| 4  | user_4   | 2009-08-30 21:51:56.381000 |
+----+----------+----------------------------+
| 5  | user_5   | 2009-09-27 21:52:13.261000 |
+----+----------+----------------------------+
| 6  | user_6   | 2009-09-30 21:52:06.509000 |
+----+----------+----------------------------+

I want to know in % with what ratio per month the number of registrations increased in relation to the previous month.
I grouped the number of registrations by month:

SELECT
  EXTRACT(YEAR FROM created_at) as YEAR,
  EXTRACT(MONTH FROM created_at) as MONTH,
  COUNT(*)
FROM users
GROUP BY EXTRACT(YEAR FROM created_at), EXTRACT(MONTH FROM created_at)
ORDER BY EXTRACT(YEAR FROM created_at), EXTRACT(MONTH FROM created_at);

Result:

+----+------+-------+-------+
| id | year | month | count |
+----+------+-------+-------+
| 1  | 2009 | 7     | 1     |
+----+------+-------+-------+
| 2  | 2009 | 8     | 2     |
+----+------+-------+-------+
| 3  | 2009 | 9     | 3     |
+----+------+-------+-------+

I can’t figure out how to calculate the ratio of the current COUNT to the previous one (for the last month)
It should look something like this:

+----+------+-------+---------+
| id | year | month | dynamic |
+----+------+-------+---------+
| 1  | 2009 | 7     | 0%      |
+----+------+-------+---------+
| 2  | 2009 | 8     | 100%    |
+----+------+-------+---------+
| 3  | 2009 | 9     | 50%     |
+----+------+-------+---------+

I would be grateful for any hints

Answer 1

SELECT
t1.YEAR,
t1.MONTH,
((t1.REGS - t2.REGS) / t1.REGS) * 100 as DYNAMIC
FROM
(
SELECT
  EXTRACT(YEAR FROM created_at) as YEAR,
  EXTRACT(MONTH FROM created_at) as MONTH,
  COUNT(*) AS REGS
FROM users
GROUP BY EXTRACT(YEAR FROM created_at), EXTRACT(MONTH FROM created_at)
) t1
LEFT JOIN
(
SELECT
  EXTRACT(YEAR FROM created_at) as YEAR,
  EXTRACT(MONTH FROM created_at) as MONTH,
  COUNT(*) AS REGS
FROM users
GROUP BY EXTRACT(YEAR FROM created_at), EXTRACT(MONTH FROM created_at)
) t2 ON t2.YEAR = t1.YEAR
  AND t2.MOUNT = (t1.MOUNT) + 1 

ORDER BY t1.YEAR,t1.MONTH;

Answer 2

In oracle, you can solve your problem with the model statement :

-- данные для выборки
with data as (
  select 1 as id, 2009 as year, 3 as month, 50  as count from dual union all
  select 2 as id, 2009 as year, 4 as month, 100 as count from dual union all
  select 3 as id, 2009 as year, 5 as month, 133 as count from dual union all
  select 4 as id, 2009 as year, 7 as month, 2   as count from dual union all
  select 5 as id, 2010 as year, 2 as month, 98  as count from dual union all
  select 6 as id, 2010 as year, 4 as month, 600 as count from dual
)

-- сам запрос
select year, month, count, round(result, 2) as increment_rate
from data
model
  -- генерируем порядковый номер для строк, 
  -- по которому будем осуществлять доступ к данным
  dimension by (row_number() over (order by year, month) rn)
  -- указываем столбцы, которые будут отображаться/участвовать в расчетах
  -- также добавляем несуществующий столбец result, 
   -- что бы в дальнейшем помещать в него результат вычислений
  measures (count, year, month, 0 result)
  rules (
    -- делаем соответствующие вычисления
    result[any] = greatest(decode(nvl(count[cv(rn)-1],0), 0, 0, count[cv(rn)] / count[cv(rn)-1])*100 - 100, 0)
  )

Result:

+------+-------+-------+---------------+
| YEAR | MONTH | COUNT | INCREMENT_RATE|
+------+-------+-------+---------------+
| 2009 | 3     | 50    |  0            |
+------+-------+-------+---------------+
| 2009 | 4     | 100   | 100           |
+------+-------+-------+---------------+
| 2009 | 5     | 133   | 33            |
+------+-------+-------+---------------+
| 2009 | 7     | 2     | 0             |
+------+-------+-------+---------------+
| 2010 | 2     | 98    |  4800         |
+------+-------+-------+---------------+
| 2010 | 4     | 600   | 512,24        |
+------+-------+-------+---------------+

Formula explanation:
result[any] = greatest(decode(nvl(count[cv(rn)-1],0), 0, 0, count[cv(rn)] / count[cv(rn)-1]) *100 - 100, 0)
To the left of the equal sign, we indicate that for each cell (keyword any ) in the result column , we evaluate the expression to the right of the equal sign as a value.
In order to calculate the growth rate , you need to take the value for the current time period, divide it by the value for the previous time period, then multiply by 100% and subtract 100%.
But since we have no previous period for the first period in the table (for the first row), and the value in the previous period can be 0, a division by 0 error occurs. Therefore, this situation needs to be handled, for which decode is used in the formula , which checks if the value of the previous period does not exist or is equal to 0, then it is necessary to return 0, if the value of the previous period is not equal to 0, then we calculate the ratio. The greatest function is also used to display 0 instead of negative values ​​(when the result of the previous period is greater than the current one, there is no increase, respectively).
If instead of growth it is necessary to calculate the percentage of growth, then the subtraction of 100% must be removed.

Answer 3

For example with a variable:

SET @prev = 0;
SELECT `year`, `month`, IF(0 = @prev, 0, `count`/@prev-1) AS `percent`, 
        @prev := `count` AS `absolute`
    FROM (
        здесь ваша выборка
    )
    ORDER BY `year`, `month`;