count of unique users for min date-CodePudding

I have data in a single table structured like so:

device type	userID	createdDate
A	jpq	2018-01-01
A	xyz	2020-01-01
B	abc	2020-01-01
C	abc	1999-01-01

I'm trying to find - for each devicetype, how many users have that devicetype as their oldest entry in the database.

My output should look like this for the example data:

count of users	device type
2	A
1	C

The query I have so far looks like this, but it also returns a min(createdDate) and I'm not confident that it's getting the count correctly.

SELECT
    count(d.UserId),
    d.deviceType,
    min(d.CreationDate)
FROM Device d
GROUP BY d.deviceType

CodePudding user response：

First you need to get appropriate rows by using row number for each user and then get your result, like this:

SELECT
    count(d.UserId),
    d.deviceType
FROM (
    SELECT
      userId,
      deviceType,
      ROW_NUMBER() OVER (PARTITION BY userId ORDER BY createdDate) rw
    FROM Device
) d
WHERE d.rw = 1
GROUP BY
  d.deviceType