Помогите исправить ошибку в моем SQL запросе:

Question

Для каждого месяца с июля 1996 года по май 1998 года посчитайте конверсию в процентах. Найдите количество уникальных компаний-заказчиков в текущем месяце. Разделите его на общее количество компаний-заказчиков Northwind, которые оформили хотя бы один заказ за всё предыдущее время, включая текущий месяц. Под уникальностью компании в этой задаче подразумевается отсутствие повторов в выборке. 
В итоговой таблице должны быть следующие поля: 
дата первого числа текущего месяца; 
количество компаний-заказчиков в текущий месяц; 
общее количество компаний-заказчиков за всё предыдущее время, включая текущий месяц; 
отношение количества покупателей за текущий месяц к общему количеству покупателей.

Спасибо!

WITH  
i AS ( 
  SELECT DATE_TRUNC('month', o.order_date::date) AS month, 
         COUNT(DISTINCT  c.company _name) AS number_of_customers 
  FROM northwind.orders AS o 
  JOIN northwind.customers AS c ON o.customer_id = c.customer_id 
  WHERE o.order_date BETWEEN '1996-07-01' AND '1998-05-01' 
  GROUP BY month), 
   
ii AS (SELECT COUNT(customer_id) as total_customers, 
              order_date 
       FROM northwind.orders 
       HAVING COUNT(DISTINCT order_id) > 1) 
 
SELECT DATE(i.month) as first_day_of_month, 
       i.number_of_customers, 
       SUM( ii.total _customers) as total_customers, 
       ROUND(number_of_customers::numeric / SUM(total_customers) OVER (ORDER BY month ) * 100, 2) AS conversion_rate 
 FROM i 
 JOIN ii ON i.order_date=ii.order_date

Sergio 2.1 · Answer

WITH 
i AS ( SELECT DATE_TRUNC(‘month’, o.order_date::date) AS month, COUNT(DISTINCT  c.company _name) AS number_of_customers FROM northwind.orders AS o JOIN northwind.customers AS c ON o.customer_id = c.customer_id WHERE o.order_date BETWEEN ‘1996-07-01’ AND ‘1998-05-01’ GROUP BY month), 
 
ii AS (SELECT COUNT(DISTINCT customer_id) as total_customers, DATE_TRUNC(‘month’, order_date::date) as month FROM northwind.orders GROUP BY month) 
 
SELECT DATE(i.month) as first_day_of_month, i.number_of_customers, SUM( ii.total _customers) OVER (ORDER BY i.month ) as total_customers, ROUND(i.number_of_customers::numeric / SUM( ii.total _customers) OVER (ORDER BY i.month ) * 100, 2) AS conversion_rate FROM i JOIN ii ON i.month=ii.month

Александр · Answer

если нет никакого желание что-либо учить, подружить с GPT, как это сделал Sergio, и пусть GPT тебе всё разжёвывает...

myodo.ru · Answer

with s as (select distinct trunc('month', order.date ) m, customer_id id from orders where date between .. and ..)
select distinct m, nvl(customer,id)cust, count(1)over(partition by m)c, count(1)over(partition by null)t from s left join customers c on  s.id =c.customer_id

.. меняй на даты
Не проверял, возможны опечатки.

Алексей · Answer

Привет. Удалось ли в этоге решить задачу? У меня сейчас такой же вопрос по ней..

Данька Горин · Answer

SELECT DISTINCT curr_month, 
       COUNT(customer_id) OVER(PARTITION BY curr_month ORDER BY curr_month) AS customers_this_month, 
       COUNT(is_repeated_customer) OVER(ORDER BY curr_month) AS total_customers, 
       ROUND((COUNT(customer_id) OVER(PARTITION BY curr_month ORDER BY curr_month) * 100.0 / COUNT(is_repeated_customer) OVER(ORDER BY curr_month)), 2) AS conversion 
       FROM ( 
SELECT DISTINCT DATE_TRUNC('month', o.order_date)::date AS curr_month, 
               customer_id, 
               CASE 
                    WHEN DATE_TRUNC('month', o.order_date)::date = FIRST_VALUE(DATE_TRUNC('month', o.order_date)::date) OVER(PARTITION BY customer_id ORDER BY o.order_date) 
                         THEN 1 
                    ELSE NULL 
               END AS is_repeated_customer 
          FROM northwind.orders AS o 
         WHERE DATE_TRUNC('month', o.order_date)::date BETWEEN '1996-07-01' AND '1998-05-01' 
         ORDER BY curr_month ) AS st1 
         ORDER BY curr_month