How to create a loop on mysql query and display all results at once

curiousIT 0
Viewed 21

I'm currently trying to get the customers' retention rates using the following query:

set @DAY1 = 0;
set @DAY30 = 0;
set @DAY90 = 0;

SET @order_month = 1;
SET @order_year = 2020;

SELECT
@DAY1/COUNT(uid) AS D1, 
@DAY30/COUNT(uid) AS D30, 
@DAY90/COUNT(uid) AS D90
FROM 
(
SELECT
uid,
case when DATEDIFF(date(lastConnection), date(accountCreation)) >= 1 then @DAY1:=@DAY1+1 END AS DAY1,
case when DATEDIFF(date(lastConnection), date(accountCreation)) >= 30 then @DAY30:=@DAY30+1 END AS DAY30,
case when DATEDIFF(date(lastConnection), date(accountCreation)) >= 90 then @DAY90:=@DAY90+1 END AS DAY90
from user
where month(accountCreation) = @order_month
and year(accountCreation) = @order_year
)
AS T1

This works as expected but what I would like to add is some sort of loop that would increment the @order_month variable by one (as well as the @order_year variable when we reach a new year). The increment would go as far as August 2022. I would like the result of each 'Select' to be displayed at once. Is that possible and if yes, how could I do that?

mysql
1 Answers

If I get your intention right, you just want to calculate the percentage of users whose account was created at least 1 day (30 days, 90 days) before their last login. And you want to calculate this for each year and month based on accountCreation, that is something like

SELECT year(accountCreation) year, 
       month(accountCreation) month,
       sum(CASE WHEN DATEDIFF(date(lastConnection), date(accountCreation)) >= 1 THEN 1 END) / count(*) AS D1,
       sum(CASE WHEN DATEDIFF(date(lastConnection), date(accountCreation)) >= 30 THEN 1 END) / count(*) AS D30,
       sum(CASE WHEN DATEDIFF(date(lastConnection), date(accountCreation)) >= 90 THEN 1 END) / count(*) AS D90
  FROM user
 GROUP BY year, month;

In other words: there's no need for a loop.

Mihe0
Related