Provide a sql query(any engine) to compute the first time a user has subscribed to the product.

Table: subscribers

Month     Year  User
January   2016  ZandraDilnot
January   2016  AlfonseMeans
January   2017  KandyChiandotto
February  2017  AlfonseMeans
February  2017  SusanRowan
March     2017  AlfonseMeans
March     2017  ZandraDilnot
March     2017  CrystalPoindexter

I tried the use of Where and group by combo to come up with logic but can't find the right query to so!

select month, year, user from subscribers where (Need to check for min year) group by user

Expected Output

Month     Year  User
January   2016  ZandraDilnot
January   2016  AlfonseMeans
January   2017  KandyChiandotto
February  2017  SusanRowan
March     2017  CrystalPoindexter
... ... ...

3 Answers

0
Community On

This may work:

SELECT User, MIN(Year),MIN(Month) FROM subscribers GROUP BY User
0
Rodney Ellis On

here is how to do with a CTE ... not ... I added an extra column with the Month as a number just so it's easier to sort without having to convert it inline.

create table #Test (m1 varchar(100) , m2 smallint ,  y int, u varchar(100));
insert into #Test (m1 , m2 , y , u) values ('January  ',1,2016,'ZandraDilnot      '       )
insert into #Test (m1 , m2 , y , u) values ('January  ',1,2016,'AlfonseMeans      '       )
insert into #Test (m1 , m2 , y , u) values ('January  ',1,2017,'KandyChiandotto   '   )
insert into #Test (m1 , m2 , y , u) values ('February ',2,2017,'AlfonseMeans      '       )
insert into #Test (m1 , m2 , y , u) values ('February ',2,2017,'SusanRowan        '   )
insert into #Test (m1 , m2 , y , u) values ('March    ',3,2017,'AlfonseMeans      '       )
insert into #Test (m1 , m2 , y , u) values ('March    ',3,2017,'ZandraDilnot      '       )
insert into #Test (m1 , m2 , y , u) values ('March    ',3,2017,'CrystalPoindexter '   )

;

with cte1 AS 
(
select * 
    ,  RANK() OVER (PARTITION BY u ORDER BY m2 , y ) AS R
From #test
)
select * 
From cte1 where R = 1;
0
Cinto On

One option is to merge the first two columns and create a date field. Then the following query:

select user, min(date) from tbl group by user

should work.