Return the period where you have the highest number of records in a table

Question

I am mounting a query from the same table of my bank that needs to return (as stated in the title) the period (monthly, for example) in which presents the largest number of entries.

It turns out that I already have an SQL mounted to count the amount of existing records from the period informed by the user. See below:

Select
    Sel.Mes,
    MAX(Sel.Total_Cadastrados)
from (
       Select
            Count(*) as Total_cadastrados,
            extract(month from p.dt_cadastro) as Mes
        from Pessoa p
        where cast(p.dt_cadastro as Date) between :ini and :fin
        order by p.dt_cadastro) Sel
group by Mes;

However I would also have to return in this same SQL which was the period (month in the case) that had the highest number of registrations. So if I select from 01/01/2021 - 03/31/2021 the above SQL will bring the sum of all entries, and that’s not what I want. See below how it looks:

I’d like a hint on how I can do this,

Thank you in advance!

Answer 1

You can do what you want in the following way:

1. Group the months and count the number of records;

2. Sort the total quantity in descending order (largest to smallest);

3. Limits the result;

 
    select DATE_FORMAT(p.dt_cadastro,'%m/%Y') as mes,
               count(*) as total_cadastrados
        from pessoa p
         where cast(p.dt_cadastro as Date) between '2020-01-01'
                                               and '2020-03-31'
         group by DATE_FORMAT(p.dt_cadastro,'%m/%Y')
        order by count(*) desc
    limit 1;
  

This way you could even, make your query more flexible... giving results like the top 5 months with more people registered, just manipulating the limit

I hope I’ve helped :)