Clever Geek Handbook

Read and highlight an SQL query

I have a table of books that contains the following columns:

Book_Id   User_Id
001       1
002       2
001       1
004       2
005       3
006       3
007       2
008       2
009       1

Where :

Book_Id - the identifier of the book the user is reading; User_Id - reader / user ID.

Suppose User1 read books three times, but 2 of them were the same, so user 1 read 2 different books (001 and 009). User 2 read 4 different books and user 3 read 2 different books. In general, there are 2 users who read 2 different books and 1 user who reads 4 different books. The expected output is as follows:

Distinct_Books_Count --- User_Count
2                           2
4                           1

I tried the following:

SELECT COUNT (DISTINCT Book_Id), COUNT (User_Id) FROM Books GROUP BY User_id

But I am getting the following table:

Distinct_Books_Count  User_Count
2                     3
4                     4
2                     2

So, any alternative solution or changes?

+3


source to share


1 answer


I call this a "histogram of histograms". You can do this using two group by

s:



SELECT num_books, COUNT(*)
FROM (SELECT b.User_Id, COUNT(DISTINCT Book_Id) as num_books
      FROM Books b
      GROUP BY User_Id
     ) b
GROUP BY num_books
ORDER BY num_books;
+2


source






More articles:


All Articles