SQL return only best performing country/segment for each month

Question

Below calculates the SalesMonth, CountryName, SegmentName, PromotionRate and TotalMonthlySales of each segment within each country for each month from 1/1/2016 onwards.

with monthly_sales_info as (
    select

Results top 3:

SalesMonth	CountryName	SegementName	PromotionRate	TotalMonthlySales
2016-03-01	Canada	Midmarket	0.106557	424880.85
2016-05-01	Canada	Midmarket	0.089285	159978
2016-02-01	France	Government	0.149821	524671.78

I now need to only return the top performing country/segment for each month from 1/1/2016. Multiple countries can have multiple segments. One segment is in one country.

score 0 · Accepted Answer · answered Sep 09 '22 at 04:41

You need to order the TotalMonthlySales value using RANK() window function

;with monthly_sales_info as (
    select
        sales_info.SalesMonth,
        c.CountryName,
        s.SegmentName,
        sales_info.PromotionRate,
        sales_info.TotalMonthlySales
    from Region r
        inner join Country c on c.CountryID = r.CountryID
        inner join Segment s on s.SegmentID = r.SegmentID
        inner join SalesRegion sr on sr.RegionID = r.RegionID
        left join (
            select
                so.SalesRegionID,
                so.SalesMonth,
                sum(case when sli.PromotionID = 0 then 0.0 else 1.0 end) /
                count(*) as PromotionRate,
                sum(SalePrice) as TotalMonthlySales
            from SalesOrder so
                inner join SalesOrderLineItem sli on sli.SalesOrderID =
                so.SalesOrderID
            group by
                so.SalesRegionID,
                so.SalesMonth
                ) sales_info on sales_info.SalesRegionID = sr.SalesRegionID
),
top_monthly_sales_info AS(
    select *,RANK() OVER(PARTITION BY YEAR(SalesMonth), MONTH(SalesMonth) ORDER BY TotalMonthlySales DESC) RankValue
    from monthly_sales_info
    where SalesMonth >= '2016-01-01')
    SELECT SalesMonth, CountryName, SegmentName, PromotionRate, TotalMonthlySales
    FROM top_monthly_sales_info
    WHERE RankValue = 1

SQL return only best performing country/segment for each month

1 Answers1