如何将这个多CTE解决方案简化为这个sql问题?

create table transactions 
(
    product_id int,
    store_id int,
    quantity int,
    price numeric
);

DML公司:

insert into transactions values
(1, 1, 10, 2),
(2, 1, 5, 2),
(1, 2, 5, 4),
(2, 2, 2, 4),
(2, 3, 1, 20),
(1, 3, 1, 8),
(2, 4, 2, 10),
(1, 5, 2, 5),
(2, 5, 1, 3),
(2, 6, 4, 8);

我想找出前三家商店的前三名产品,这两个都是基于销售额。我的解决方案是使用cte,如下所示:

with cte as 
(
    select store_id, rank_store 
    from
        (select 
             *,
             dense_rank() over(order by sale desc) as rank_store
         from 
             (select 
                  store_id, sum(quantity * price) as sale 
              from transactions 
              group by 1) t) t2
    where 
        rank_store <= 3
),
cte2 as 
(
    select 
        a.store_id, a.product_id, 
        sum(a.quantity * a.price) as sale_store_product
    from 
        transactions as a 
    join 
        cte as b on a.store_id = b.store_id
    group by 
        1, 2
    order by 
        1, 2
),
cte3 as 
(
    select 
        *,
        dense_rank() over (partition by store_id order by sale_store_product desc) as rank_product
    from 
        cte2
)
select * 
from cte3 
where rank_product <= 3;

预期结果如下:

基本上,第一个cte是根据我使用的销售金额获得前三名的商店 dense_rank() 窗口功能处理领带案件。然后第二个cte是前三名的商店的产品和他们的总销售额。最后一个cte是 窗口功能,再次排名的产品在每个商店的基础上,他们的销售额。然后我的最后一个查询是根据销售额得到每家商店前三名的产品。

我想知道这是否可以改进一点,因为我觉得三个CTE有点太复杂了。感谢分享任何解决方案和想法。谢谢。