Use LEAD to catch the next date

Question

I am trying to set up a query that will answer the following question:

Number of customers (registration in the case) that has a reading date of 26 days, number of customers that has reading of 27 days and so goes up to 31 days, I will show an image of exactly what I want:

My query is not returning what I want, it is returning zero in every column of QTD_DIAS, the query is as per the image (I placed after the order by a dat_leitura, ta)

So I believe I need to do some sort of subquery, but I don’t know where to start, could anyone help me? Thank you, thank you very much!

select distinct top 10 sigla_unidade AS UNIDADE
                , dsc_localidade as LOCALIDADE
                , o.competencia AS COMPETENCIA
                --, DAT_LEITURA AS QTD_DIAS
                , lead(dat_leitura, 1) over (PARTITION by dat_leitura order by) prox_leitura
                , datediff(day, dat_leitura, lead(dat_leitura, 1) over(PARTITION by dat_leitura)) QTD_DIAS
                , o.inscricao as QTD_INSCRICOES
from BASE o
join USUARIOS u 
on o.inscricao = u.inscricao
join UNIDADES n
on u.cod_und_negocio = n.cod_unid_negocio
join LOCALIDADES 1
on u.localidade = 1.cod_localidade
--group by sigla_unidade, desc_localidade, o.competencia, dat_leitura, a.inscricao

Answer 1

Considering the information provided so far, here is a suggestion:

-- código #1
with Base2 as (
SELECT *,
       datediff(day,
                lag(dat_leitura, 1) over (partition by inscricao order by competencia),
                dat_leitura
               ) as QTD_DIAS
  from BASE
)
SELECT sigla_unidade AS UNIDADE,
       dsc_localidade as LOCALIDADE,
       o.competencia AS COMPETENCIA,
       QTD_DIAS,
       count(*) as QTD_INSCRICOES
  from BASE2 as o
       join USUARIOS as u on o.inscricao = u.inscricao
       join UNIDADES as n on u.cod_und_negocio = n.cod_unid_negocio
       join LOCALIDADES as l on u.localidade = l.cod_localidade
  where QTD_DIAS between 26 and 31
  group by sigla_unidade, desc_localidade, o.competencia, QTD_DIAS;