有一张table
小提琴
或者

CREATE TABLE public.products
(product_id INT4,
sku INT4 NOT NULL,
mp VARCHAR,
cost FLOAT NOT NULL DEFAULT 0,
start_date TIMESTAMP NOT NULL,
finish_date TIMESTAMP NOT NULL);

INSERT INTO public.products
(product_id, sku, mp, cost, start_date, finish_date)
VALUES
(221, 378264, 'ozon', 47.5, '2023-02-27 21:47', '2023-02-28 00:00'),
(147, 235234, 'wb', 524.22, '2023-04-02 15:23', '2023-04-10 00:00'),
(11, 124321, 'yandex', 214.0, '2023-01-04 11:02', '2023-01-11 00:00'),
(94, 145434, 'wb', 118.24, '2023-02-19 17:11', '2023-02-21 00:00'),
(543, 124432, 'yandex', 749.0, '2023-02-12 09:12', '2023-02-15 00:00'),
(45, 798623, 'ozon', 347.12, '2023-01-13 13:45', '2023-01-16 00:00'),
(47, 123486, 'ozon', 522.2, '2023-04-01 12:45', '2023-04-16 00:00'),
(89, 123135, 'yandex', 347.8, '2023-04-02 11:13', '2023-04-16 00:00');

CREATE TABLE public.events
(sku INT4 NOT NULL,
mp VARCHAR,
status VARCHAR,
quantity INT4 NOT NULL,
create_date TIMESTAMP NOT NULL);

INSERT INTO public.events
(sku, mp, status, quantity, create_date)
VALUES
(378264, 'ozon', 'заказ товара', 2, '2023-02-28 14:47'),
(378264, 'ozon', 'покупка товара', 1, '2023-02-28 19:25'),
(235234, 'wb', 'заказ товара', 10, '2023-04-03 11:10'),
(235234, 'wb', 'покупка товара', 2, '2023-04-03 19:13'),
(124321, 'yandex', 'заказ товара', 6, '2023-01-05 10:00'),
(124321, 'yandex', 'покупка товара', 2, '2023-01-05 16:17'),
(145434, 'wb', 'заказ товара', 1, '2023-02-20 14:37'),
(145434, 'wb', 'отмена', 1, '2023-02-20 15:19'),
(124432, 'yandex', 'заказ товара', 4, '2023-02-13 09:12'),
(124432, 'yandex', 'покупка товара', 4, '2023-02-13 10:22'),
(798623, 'ozon', 'заказ товара', 10, '2023-01-14 12:21'),
(798623, 'ozon', 'покупка товара', 8, '2023-01-14 13:10'),
(123486, 'ozon', 'заказ товара', 12, '2023-04-02 14:52'),
(123486, 'ozon', 'покупка товара', 3, '2023-04-02 14:33'),
(123135, 'yandex', 'заказ товара', 5, '2023-04-04 09:00'),
(123135, 'yandex', 'покупка товара', 4, '2023-04-04 09:12');

CREATE TABLE public.stock 
(product_id INT4 NOT NULL,
stock_code VARCHAR NOT NULL,
quantity INT4,
update_date TIMESTAMP NOT NULL);

INSERT INTO public.stock
(product_id, stock_code, quantity, update_date)
VALUES
(221, 'OZON_ЦЕНТРАЛЬНЫЙ_СКЛАД', 24, '2023-02-26 00:00'),
(147, 'WB_ХОРВУГИНО', 12, '2023-04-03 00:00'),
(11, 'YANDEX_ХИМКИ', 340, '2023-01-03 00:00'),
(94, 'WB_ДОМОДЕДОВО', 15, '2023-02-18 00:00'),
(543, 'YANDEX_МЯКИНИНО', 27, '2023-02-11 00:00'),
(45, 'OZON_ЧЕРТАНОВО', 74, '2023-01-12 00:00'),
(47, 'ozon', 52, '2023-04-01 00:00'),
(89, 'yandex', 9, '2023-04-01 00:00');

并请求

WITH ordered AS
  (SELECT create_date, mp, SUM(quantity)::DECIMAL AS sum_ordered 
  FROM public.events
  WHERE status = 'заказ товара'
  AND DATE_PART('month', create_date) = DATE_PART('month', CURRENT_DATE)
  GROUP BY create_date, mp),

sales AS 
  (SELECT create_date, mp, SUM(quantity)::DECIMAL AS sum_sale 
  FROM public.events
  WHERE status = 'покупка товара'
  AND DATE_PART('month', create_date) = DATE_PART('month', CURRENT_DATE)
  GROUP BY create_date, mp)

SELECT
  b.create_date AS Date,
  b.mp AS Marketplace,
  ROUND(SUM(a.cost::DECIMAL * quantity::DECIMAL), 2) AS day_revenue_sum,
  CONCAT(ROUND(sales.sum_sale::DECIMAL / ordered.sum_ordered::DECIMAL, 2) * 100, '%') AS ratio
FROM
  ordered, sales, public.products AS a
RIGHT JOIN 
  public.events AS b
  ON a.sku = b.sku
WHERE 
  status = 'покупка товара' AND
  DATE_PART('month', b.create_date) = DATE_PART('month', CURRENT_DATE)
GROUP BY
  b.mp, Date
ORDER BY 
  day_revenue_sum DESC

如果没有GROUP BYratio，我就不能发出这个请求，但是使用group by ratio，我得到了一个有很多行的不正确的表。

我尝试使用ratio创建第三个sub_query，但它不起作用，让我再次使用GROUP BY ratio。最终的表应该由三行（三个市场）和列组成：

date，marketplace，daily_revenue，销售_order_ratio。

请帮帮我