How to count new users by min date without using a subquery?-CodePudding

I have an event table in which each user has several dates registered. The minimum date for each user would be the join_date for each user. I need to do a count for each user for each min date (join_date). I have achieved what I intend with the following query with a subquery in the from:

SELECT COUNT(*) AS new_users, join_date
FROM (SELECT id, min(idate) AS join_date FROM t1 GROUP BY id ORDER BY idate) AS nu
GROUP BY nu.join_date;

My question about this is the following... Is it possible to do what I need without using a subquery? I have been trying using an inner join but without satisfactory results.

I leave you the creation of the table, with data to insert as well as a link to sqlfiddle with the schema created so that you can try it.

CREATE TABLE t1 (
    idate DATE, 
    id INT
);

INSERT INTO t1 (idate, id)  VALUES
('2017-01-01', 1),('2017-01-02', 1),('2017-01-03', 1),('2017-01-04', 1),('2017-01-05', 1),
('2017-01-01', 2),('2017-03-04', 2),('2017-04-06', 2),('2017-07-08', 2),('2017-08-12', 2),
('2017-01-01', 3),('2017-02-02', 3),('2017-08-03', 3),('2017-09-12', 3),('2017-11-11', 3),
('2017-01-02', 4),('2017-03-02', 4),('2017-03-13', 4),('2017-04-04', 4),('2017-05-05', 4),
('2017-01-03', 5),('2017-01-12', 5),('2017-05-03', 5),('2017-09-04', 5),('2017-10-05', 5),
('2017-01-03', 6),('2017-01-05', 6),('2017-01-07', 6),('2017-01-08', 6),('2017-01-10', 6),
('2017-01-04', 7),('2017-01-11', 7),('2017-01-13', 7),('2017-01-14', 7),('2017-01-15', 7),
('2017-01-05', 8),('2017-01-22', 8),('2017-01-23', 8),('2017-02-15', 8),('2017-02-25', 8),
('2017-01-05', 9),('2017-01-06', 9),('2017-01-07', 9),('2017-01-08', 9),('2017-01-09', 9);

http://sqlfiddle.com/#!9/ef11d6

Thank you very much in advance.

CodePudding user response：

Use COUNT() window function to aggregate over the min dates:

SELECT DISTINCT 
       COUNT(*) OVER (PARTITION BY MIN(idate)) new_users,
       MIN(idate) join_date
FROM t1
GROUP BY id;

See the demo.