ratings view

 WITH grouped_orders AS (
 SELECT order_item.pizza,
    sum(order_item.count) AS dow_sold,
    sum(pizza.price * order_item.count::numeric * (1::numeric - customer.discount / 100::numeric)) AS dow_profit,
    pizza.price,
    date_part('dow'::text, "order".date) AS week
   FROM "order"
     JOIN order_item ON "order".id = order_item.order_id
     JOIN pizza ON order_item.pizza = pizza.name
     JOIN customer ON "order".customer = customer.phone_number
  WHERE "order".status = 'выполнен'::order_status
  GROUP BY order_item.pizza, pizza.price, (date_part('dow'::text, "order".date))
), total_profit AS (
 SELECT grouped_orders.pizza,
    sum(grouped_orders.dow_sold) AS total_sold,
    sum(grouped_orders.dow_profit) AS total_profit
   FROM grouped_orders
  GROUP BY grouped_orders.pizza
), pizzas_with_rank AS (
 SELECT total_profit.pizza,
    total_profit.total_sold,
    total_profit.total_profit,
    row_number() OVER (ORDER BY total_profit.total_sold DESC) AS rank_by_count,
    row_number() OVER (ORDER BY total_profit.total_profit DESC) AS rank_by_profit
   FROM total_profit
), rating AS (
 SELECT DISTINCT grouped_orders.pizza,
    grouped_orders.price,
    pizzas_with_rank.total_sold,
    pizzas_with_rank.rank_by_count,
    pizzas_with_rank.rank_by_profit,
    first_value(grouped_orders.week) OVER (PARTITION BY grouped_orders.pizza ORDER BY grouped_orders.dow_sold DESC) AS popular_dow
   FROM grouped_orders
     JOIN pizzas_with_rank USING (pizza)
)
CREATE VIEW ratings AS
SELECT
    rating.pizza,
    rating.price,
    rating.total_sold,
    rating.rank_by_count,
    rating.rank_by_profit,
    (ARRAY[
        'Monday'::text,
        'Tuesday'::text, 'Wednesday'::text, 'Thursday'::text, 'Friday'::text, 'Saturday'::text, 'Sunday'::text])[rating.popular_dow] AS most_popular_dow
FROM rating;