Select earliest date and count rows in table with duplicate IDs

Question

I have a table called table1:

  id   created_date
1001     2020-06-01
1001     2020-01-01
1001     2020-07-01
1002     2020-02-01
1002     2020-04-01
1003     2020-09-01

I'm trying to write a query that provides me a list of distinct IDs with the earliest created_date they have, along with the count of rows each id has:

  id   created_date  count
1001     2020-01-01      3
1002     2020-02-01      2
1003     2020-09-01      1

I managed to write a window function to grab the earliest date, but I'm having trouble figuring out where to fit the count statement in one:

SELECT 
    id,
    created_date

FROM (  SELECT 
            id, 
            created_date,
            row_number() OVER(PARTITION BY id ORDER BY created_date) as row_num
        FROM table1)
    ) AS a

WHERE row_num = 1

Answer 1

You would use aggregation:

select id, min(create_date), count(*)
from table1
group by id;

I find it amusing that you want to use window functions -- which are considered more advanced -- when lowly aggregation suffices.