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

From Martin Movsisyan, 5 Months ago, written in SQL, viewed 103 times. This paste is a reply to Re: SQL Задача 35 from Martin Movsisyan - go back
URL http://codebin.org/view/c0c08b98/diff Embed
Viewing differences between Re: SQL Задача 35 and Re: Re: SQL Задача 35
SELECT WITH a AS
(SELECT 
CAST(DATE_TRUNC('month', order_date) AS date) AS month
       ,COUNT(DISTINCT customer_id) AS customers_this_month
       ,SUM(COUNT(DISTINCT customer_id)) OVER (ORDER BY CAST(DATE_TRUNC('month', order_date) AS date)) AS total_customers\r\n       ,ROUND(COUNT(DISTINCT customer_id)/SUM(COUNT(DISTINCT customer_id)) OVER (ORDER BY CAST(DATE_TRUNC('month', order_date) AS date))*100,2) AS conversion\r\nFROM northwind.orders
WHERE CAST(DATE_TRUNC('month', order_date) AS date) BETWEEN '1996-07-01' AND '1998-05-31'
GROUP BY CAST(DATE_TRUNC('month', order_date) AS date)
ORDER BY CAST(DATE_TRUNC('month', 
date)),
b AS
(SELECT customer_id, MIN(CAST(DATE_TRUNC('month', 
order_date) AS date)date)) AS month
FROM northwind.orders
GROUP BY customer_id
ORDER BY 2),
c AS
(SELECT month, SUM(COUNT(customer_id)) OVER (order by month) AS total_customers
FROM b
GROUP BY month)

SELECT a.month, a.customers_this_month, c.total_customers, ROUND(a.customers_this_month*1.0/c.total_customers*100,2)
FROM a
LEFT JOIN C ON a.month=c.month
ORDER BY a.month

Replies to Re: Re: SQL Задача 35 rss

Title Name Language When
Re: Re: Re: SQL Задача 35 Scorching Mockingbird sql 4 Weeks ago.

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

Here you can reply to the paste above