CODEWITHSUNDEEP
sql-server-2008
Manoj
Manoj

Reputation: 883

Retrieve most popular user

I am writing a query to retrieve most popular user of my website who has purchased most books. The number should be dynamic like Top 100, Top 50 Or Top 10 users. I have written below query for the purpose but find a problem : 

DECLARE @TOPNumber INT
SET @TOPNumber = 10
SELECT 
    S.UserName,
    S.Email,
    TOPST.NumberOfPurchase
    FROM 
    Subscriber S
    JOIN 
    (
        SELECT ROW_NUMBER() Over(order by [FK_Subscriber])as Slno, FK_Subscriber,COUNT(*)     NumberOfPurchase FROM SubscriberPurchase
        GROUP BY FK_Subscriber
        --ORDER BY FK_Subscriber
    ) TOPST ON S.PK_SubscriberID = TOPST.FK_Subscriber AND TOPST.Slno <= @TOPNumber

ORDER BY TOPST.NumberOfPurchase DESC

As you can see the ORDER BY clause in inner query is commented as I can not use it in that place. If I could then the query gives me desired result.

Can anyone help how to do this. I have millions of records in database so need proper opimized

Upvotes: 0

Views: 36

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

Try this instead:

WITH CTE
AS
(
    SELECT 
      S.UserName,
      S.Email,
      p.NumberOfPurchase,
      ROW_NUMBER() Over(PARTITION BY FK_Subscriber
                        ORDER BY [FK_Subscriber])as Slno, 
    FROM Subscriber S
    INNER JOIN
    (
       SELECT FK_Subscriber, COUNT(*) NumberOfPurchase
       FROM SubscriberPurchase 
       GROUP BY FK_Subscriber
    ) AS p ON S.PK_SubscriberID = p.FK_Subscriber
)
SELECT
  UserName,
  Email,
  NumberOfPurchase
FROM CTE
WHERE Slno  <= @TOPNumber
ORDER BY NumberOfPurchase DESC

Upvotes: 1

Related Questions