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.