如何从数据集中筛选每年每月最高盈利客户的计费与非计费工时？

我需要从数据集中确定最高盈利的客户。目前得到的结果存在问题：计费列显示工时，但非计费列为空；查询返回所有客户而非每月最佳客户等。

以下是我当前编写的查询，该查询已实现将计费与非计费工时显示在同一行：

Select
    date_format(date, '%Y-%m') AS "yearMonth", 
    ClientNumber AS "Client Number",
    Sum(Case When BillType = 'Billable' Then Hours End) As "Billable Hours",
    Sum(Case When BillType = 'NonBillable' Then Hours End) As "Non-Billable Hours",
    Sum(Hours) As "Total Hours",
From Towson
WHERE ClientID
Group By
    yearMonth
Order By
    yearMonth;

查询结果如下：

该查询的年月维度显示正确（共36个月），但它汇总了每月所有客户的数据，并未筛选出每月最佳客户，这是我遇到的主要问题（此前的问题是无法将计费与非计费工时放在同一行）。

更新 #

在FanoFN的帮助下，我对查询进行了调整，解决了同一客户每月都被选为第一行的问题，确保每月TotalAmount最高的客户排在第一行：

WITH cte AS 
(SELECT
    DATE_FORMAT(DATE, '%Y-%m') AS "yearMonth",
    ClientId AS "Client ID",
    COUNT(DISTINCT EmployeeId) AS "Employees on account",
    SUM(CASE WHEN BillType = 'Billable' THEN Hours END) AS "Billable Hours",
    SUM(CASE WHEN BillType = 'NonBillable' THEN Hours END) AS "Non-Billable Hours",
    SUM(Hours) AS "Total Hours",
    SUM(TotalAmount) AS "Total Amount",
    ROW_NUMBER() OVER (PARTITION BY DATE_FORMAT(DATE, '%Y-%m') ORDER BY SUM(TotalAmount) DESC) AS RowNum
FROM Towson
GROUP BY
    yearMonth, ClientId
ORDER BY
     yearMonth, TotalAmount)
    
SELECT *
FROM cte 
WHERE RowNum=1;

查询结果如下（仅展示前三个月）：

非计费工时显示为null是因为这些客户没有非计费工时（已在数据集中确认）。

内容的提问来源于stack exchange，提问作者SGT Mc Cool