Per-product performance with share % and rank (Aug-2025)

Question: For Aug-2025, for each product—orders count, total sales, average order, % of overall, rank by total; also tag the top product.

WITH product_stats AS (
  SELECT
    product,
    COUNT(*)    AS orders,
    SUM(amount) AS total,
    AVG(amount) AS avg_order
  FROM sales
  WHERE sales_date BETWEEN '2025-08-01' AND '2025-08-31'
  GROUP BY product
),
grand AS (
  SELECT SUM(amount) AS grand_total
  FROM sales
  WHERE sales_date BETWEEN '2025-08-01' AND '2025-08-31'
),
ranked AS (
  SELECT
    ps.*,
    RANK() OVER (ORDER BY total DESC) AS rnk,
    (SELECT grand_total FROM grand)    AS grand_total
  FROM product_stats ps
)
SELECT
  product,
  orders,
  total,
  ROUND(avg_order, 2)                           AS avg_order,
  ROUND(100.0 * total / grand_total, 2)         AS pct_of_total,
  rnk                                           AS rank_by_sales,
  CASE WHEN rnk = 1 THEN 'Top product' ELSE '' END AS tag
FROM ranked
ORDER BY total DESC;