SQL Interview Questions | How to identify the new Users and repeated users in SQL | SQL | SQL Server

create table login
(ldate date,cust_id varchar(20))
insert into login values('2024-01-01','C1'),
insert into login values('2024-01-01','C2'),
insert into login values('2024-01-01','C3'),
insert into login values('2024-01-01','C4'),
insert into login values('2024-01-02','C5'),
insert into login values('2024-01-02','C1'),
insert into login values('2024-01-03','C6'),
insert into login values('2024-01-03','C2'),
insert into login values('2024-01-03','C7'),
insert into login values('2024-01-04','C1'),
insert into login values('2024-01-04','C2'),
insert into login values('2024-01-04','C8'),

[chandanpatra]

please provide the create & insert statement in the description box ....

[vasanthkumar-zwxf]

with cte as (
select min(ldate) mdate, cust_id from login group by cust_id)
select l.ldate,
sum(case when c.mdate=l.ldate then 1 else 0 end) new_cust,
sum(case when c.mdate<>l.ldate then 1 else 0 end) reapet_cust
from cte c inner join login l
on c.cust_id=l.cust_id
group by l.ldate;