I wrote a query to show something like I have below.

I'm trying to just show the channel that had the most upgrades and disconnect per fiscal month in each region. I'm stuck in the sense that there are multiple regions and multiple channels. Let me know what you guys think. Thanks!!

I tried making the original query a cte and querying that with a group by involved but couldn't figure it out.

1/1/2022 |   a    |  Indirect |  1000    |   600 
1/1/2022 |   b    |  outbound |  6000    |   450
2/1/2022 |   a    |  indirect |  1100    |   25
2/1/2022 |   b    |  outbound |  400     |   55

The output should be something like what's above. Basically, I need to show the top-performing channel from each month in upgrades and disconnects. I'm also realizing I may need another channel column for discos.

CodePudding user response：

Try a SORT BY function (SORT BY (Desired Sorting Column) DESC) to put the highest performing regions in order?

CodePudding user response：

According to your expected output, you could use the max window function as the following:

with max_cte as
(
  select *,
    max(total_upgrades) over (partition by date, region) mx_total_upgrades,
    max(total_disconnect) over (partition by date, region) mx_total_disconnect
  from table_name -- replace table_name with your cte name
)
select date, region, channel, mx_total_upgrades, mx_total_disconnect
from max_cte
where total_upgrades = mx_total_upgrades
order by date, region

See demo