Skip to content
Advertisement

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.

Advertisement

Answer

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.

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement