Need number of returned customers at the end of every month for two years-CodePudding

select count(distinct t.user)
from  (
select l.user_id as 'user', l.code as 'loan', ifnull(max(DATEDIFF(ifnull(pr.repaidAt,now()),pr.payAt)),0) as 'days'
from Account a
join Loan l on l.account_id = a.id
join User u on l.user_id = u.id
left join PaymentRequest pr on pr.referredInstance_uuid = l.uuid and pr.requestType = 'cre' and pr.requestState in ('pel','pad')
where l.productType = 'per' and l.repaidAt <= '2021-12-31 23:59:59' and l.user_id not in (select user_id from Loan where repaidAt >= '2021-12-31 23:59:59' or repaidAt is null and user_id=l.user_id and createdAt <= '2021-12-31 23:59:59')
group by l.code) t
where t.days <= 90;

Need to change dates in query to syntax to get the result like this

2021.12.31 1794 
2021.11.30 1805 
2021.10.31 1781 
2021.09.30 1761 
2021.08.31 1746 
2021.07.31 1732 
2021.06.30 1686 
2021.05.31 1659 
2021.04.30 1616 
2021.03.31 1591 
2021.02.28 1560 
2021.01.31 1533 
2020.12.31 1503 
2020.11.30 1461 
2020.10.31 1411 
2020.09.30 1397 
2020.08.31 1344 
2020.07.31 1310 
2020.06.30 1256 
2020.05.31 1255 
2020.04.30 1226 
2020.03.31 1204 
2020.02.28 1151 
2020.01.31 1091

I have a query to get one month number of returned customers. Need to change it to get data for last 2 years by every month. Could someone help me?

CodePudding user response：

I have a query to get one month number of returned customers. Need to change it to get data for last 2 years by every month. Could someone help me?

CodePudding user response：

To change the date format you should use DATE_FORMAT function

For example:

SELECT DATE_FORMAT("2017-06-15", "%Y.%m.%d");

Would result in 2017.06.15

In your case should be something like:

select date_format(your_date_column,"%Y.%m.%d"),count(distinct t.user)
from  (
select l.user_id as 'user', l.code as 'loan', ifnull(max(DATEDIFF(ifnull(pr.repaidAt,now()),pr.payAt)),0) as 'days'
from Account a
join Loan l on l.account_id = a.id
join User u on l.user_id = u.id
left join PaymentRequest pr on pr.referredInstance_uuid = l.uuid and pr.requestType = 'cre' and pr.requestState in ('pel','pad')
where l.productType = 'per' and l.repaidAt <= '2021-12-31 23:59:59' and l.user_id not in (select user_id from Loan where repaidAt >= '2021-12-31 23:59:59' or repaidAt is null and user_id=l.user_id and createdAt <= '2021-12-31 23:59:59')
group by l.code) t
where t.days <= 90;

CodePudding user response：

You can do something like this in your main select clause:

select concat(date(repaidAt), ' ', count(distinct t.user));

But to support that, you will need to select repaidAt in the subquery and maybe you need to either use an aggregate function, such as max or you need to add it to your group by, depending on what your actual requirements are.