sql

 WITH filtered AS (

    SELECT *

    FROM Kurtis

    WHERE stock_quantity > 20

      AND color IN ('Red', 'Green')

      AND date_added BETWEEN DATE '2024-08-01' AND DATE '2024-11-01'

),

ranked AS (

    SELECT *,

           DENSE_RANK() OVER (

               PARTITION BY brand, size

               ORDER BY price DESC

           ) AS rnk

    FROM filtered

),

selected AS (

    SELECT *

    FROM ranked

    WHERE rnk = 1

),

stock_totals AS (

    SELECT brand,

           size,

           SUM(stock_quantity) AS total_stock

    FROM selected

    GROUP BY brand, size

)

SELECT

    s.brand,

    s.size,

    s.color,

    s.price,

    t.total_stock

FROM selected s

JOIN stock_totals t

  ON s.brand = t.brand

 AND s.size = t.size

ORDER BY

    s.brand,

    t.total_stock DESC,

    s.price DESC;

SELECT

    k.brand,

    k.size,

    k.color,

    k.price,

    s.total_stock

FROM Kurtis k

JOIN (

    SELECT

        brand,

        size,

        SUM(stock_quantity) AS total_stock

    FROM Kurtis

    WHERE stock_quantity > 20

      AND color IN ('Red', 'Green')

      AND date_added BETWEEN '2024-08-01' AND '2024-11-01'

    GROUP BY brand, size

) s

ON k.brand = s.brand

AND k.size = s.size

WHERE k.stock_quantity > 20

  AND k.color IN ('Red', 'Green')

  AND k.date_added BETWEEN '2024-08-01' AND '2024-11-01'

  AND k.price = (

      SELECT MAX(price)

      FROM Kurtis k2

      WHERE k2.brand = k.brand

        AND k2.size = k.size

        AND k2.stock_quantity > 20

        AND k2.color IN ('Red', 'Green')

        AND k2.date_added BETWEEN '2024-08-01' AND '2024-11-01'

  )

ORDER BY k.brand, s.total_stock DESC, k.price DESC;