如何找到新客户？

Question

I ran into one problem that I can not solve for 3 days already. The problem is current, there is a database of two columns of date ("yyyy-mm") and the phone number is unique non-repeating. I want to find out how many new customers each month. Well, the formula is roughly current (the new client is the first month + the new client is the next month ....). If the customer meets the first month he is considered a new customer, then it is on the other month, he is missing a new customer.

Answer 1

try Something like this:

select year(f2.DateColumn) YearCol,  month(f2.DateColumn) MonthCol, count(f2.PhoneNumber) NbNewPhone
from (
        Select f1.*,  row_number() over(partition by f1.PhoneNumber order by f1.DateColumn) rang
        from yourTable f1
) f2 
where f2.rang=1
group by year(f2.DateColumn),  month(f2.DateColumn)
order by 1, 2

or if you column is not a date :

select f2.DateColumn, count(f2.PhoneNumber) NbNewPhone
from (
        Select f1.*,  row_number() over(partition by f1.PhoneNumber order by f1.DateColumn) rang
        from yourTable f1
) f2 
where f2.rang=1
group by f2.DateColumn
order by 1, 2