CODEWITHSUNDEEP
sqlsql-servert-sqlsubquerygreatest-n-per-group
JoeW
JoeW

Reputation: 39

How can I pull out the second highest product usage from a SQL Server table?

We have a product usage table for software. It has 4 fields, [product name], [usage month], [users] and [Country]. We must report the data by Country and Product Name for licensing purposes. Our rule is to report the second highest number of users per country for each product. The same products can be used in all countries. It based on monthly usage numbers, so second peak usage for fy 2020. Since all of the data is in one table I am having trouble figuring out the SQL to get the information I need from the table.

I am thinking I need to do multiple selects (inner select? ) and group the data in a way to pull out the product name, peak usage and country. But that is where I am getting confused as to the best approach.

Example Data looks like this:

[product name], [usage month], [users], [Country]
Product1    January 831 United States of America 
Product1    December    802 United States of America
Product1    September   687 United States of America
Product1    August  407 United States of America
Product1    July    799 United States of America
Product1    June    824 United States of America
Product1    April   802 United States of America
Product1    May 796 United States of America
Product1    February    847 United States of America
Product1    March   840 United States of America
Product1    November    818 United States of America
Product1    October 841 United States of America
Product2    March   1006    United States of America
Product2    February    1076    United States of America
Product2    April   890 United States of America
Product2    May 831 United States of America
Product2    September   538 United States of America
Product2    October 1053    United States of America
Product2    July    673 United States of America
Product2    August  87  United States of America
Product2    November    994 United States of America
Product2    January 1042    United States of America
Product2    December    952 United States of America
Product2    June    873 United States of America

I had originally thought about breaking this out into multiple tables and then trying sql against each product table, but since this is something I will need to do monthly, I didn't want to redesign the ETL that loads the data because 1) I don't control that ETL and 2) I felt like that would be a move backwards for a repetitive task. We were also looking into Power BI to do this for us, but haven't foound the right approach, and I would honestly rather have this in SQL.

Upvotes: 0

Views: 63

Answers (3)

XLars
XLars

Reputation: 177

You can use this, it returns the second highest user count grouped by first country and second product. Take as note that when there is only 1 user count per country and product the it will not show up, there have to be at least two user counts per country and product.

SELECT 
    country, product, users 
FROM 
    ProductCounts
WHERE 
   (SELECT COUNT(*) FROM ProductCounts AS p 
    WHERE 
        p.country = ProductCounts.country 
    AND 
        p.product = ProductCounts.product
    AND 
        p.users >= ProductCounts.users ) = 2
GROUP BY
    country, product

Upvotes: 0

Gregory A Beamer
Gregory A Beamer

Reputation: 17010

For one country it should be fairly simple. This is off the top of my head, but a bit of tweaking should do it. This comes from your table names, which is likely way off (right?).

SELECT top 2 users
FROM ProductCounts
WHERE County = @Country
ORDER BY users DESC
LIMIT 1;

I don't really get a sense of how your data is entered to get a good feel of a better way to store the data to get the information you desire for your report.

Upvotes: 0

GMB
GMB

Reputation: 222462

If I follow you correctly:

select *
from (
    select t.*,
        row_number() over(partition by product_name, country order by users desc) rn
    from mytable t
) t
where rn = 2

This generates one row per product and country, that corresponds to the second highest number of users.

Upvotes: 3

Related Questions