Query that returns the 10th and 100th post id from a user - data explorer

Question

I am writing a query that will take in the Posts and Users tables from the data explorer schema and output the 10th and 100th post id from every user. The output would be a column for the 10th or 100th post id and a second column saying whether it is the 10th or 100th post from that user.

I'm having trouble wrapping my head around how to get the 10th and 100th post from every user. I've started by counting the number of posts per user but my query is still not grouping all user ids together.

SELECT u.Id, u.DisplayName, p.Id, COUNT(p.Id) as numPost
FROM Users AS u
LEFT JOIN Posts AS p ON u.Id = p.OwnerUserId
GROUP BY u.Id, u.DisplayName, p.Id

I'm expecting the function to output the number of posts per user and seeing if I can extract the 10th and 100th post from there but I think I am going about it in the wrong direction. Current query output

Any help would be appreciated!

Answer 1

I suggest using row_number() over(...) for this:

SELECT
    uid
  , displayname
  , MAX(CASE WHEN rn = 10  THEN pid END) AS pid10
  , MAX(CASE WHEN rn = 100 THEN pid END) AS pid100
FROM (
    SELECT
        u.Id                                                AS uid
      , u.DisplayName
      , p.Id                                                AS pid
      , ROW_NUMBER() OVER (PARTITION BY u.Id ORDER BY p.id) AS rn
    FROM Users AS u
    LEFT JOIN Posts AS p ON u.Id = p.OwnerUserId
    ) AS up
WHERE (
        rn IN (10, 100)
     OR rn IS NULL
      )
GROUP BY
    uid
  , displayname

I assume the left join to posts is because you want all users returned, if so please note the OR RN IS NULL in the where clause will be needed for any users with no posts.

demo