Re: Re: SQL Задача 35

From Martin Movsisyan, 2 Months ago, written in SQL, viewed 65 times. This paste is a reply to Re: SQL Задача 35 from Martin Movsisyan - view diff
URL Embed
Download Paste or View Raw
  1. WITH a AS
  2. (SELECT CAST(DATE_TRUNC('month', order_date) AS DATE) AS MONTH
  3.        ,COUNT(DISTINCT customer_id) AS customers_this_month
  4. FROM northwind.orders
  5. WHERE CAST(DATE_TRUNC('month', order_date) AS DATE) BETWEEN '1996-07-01' AND '1998-05-31'
  6. GROUP BY CAST(DATE_TRUNC('month', order_date) AS DATE)),
  7. b AS
  8. (SELECT customer_id, MIN(CAST(DATE_TRUNC('month', order_date) AS DATE)) AS MONTH
  9. FROM northwind.orders
  10. GROUP BY customer_id
  11. ORDER BY 2),
  12. c AS
  13. (SELECT MONTH, SUM(COUNT(customer_id)) OVER (ORDER BY MONTH) AS total_customers
  14. FROM b
  17. SELECT a.month, a.customers_this_month, c.total_customers, ROUND(a.customers_this_month*1.0/c.total_customers*100,2)
  18. FROM a
  19. LEFT JOIN C ON a.month=c.month
  20. ORDER BY a.month

Reply to "Re: Re: SQL Задача 35"

Here you can reply to the paste above