There is a Sales table with two columns: Sales date and customer. a SQL statement is required for calculation.
There is a Sales table with two columns: Sales date and customer. a SQL statement is required for calculation.
The Code is as follows:
--- 1. average sales wait time
--- There is a Sales table with two columns: Sales date and customer. a SQL statement is required for calculation.
-- Average number of days between two purchases by each customer
-- Assume that the same person will not buy twice a day
Create table sales
(
Custname varchar (10) not null,
Saledate datetime not null
)
Go
Insert sales
Select 'zhang san', '2014-1-1-1 'union
Select 'zhang san', '2017-11-1 'union
Select 'zhang san', '2014-1-1-1 'union
Select 'wang 5', '2017-2-1 'union
Select 'wang 5', '2017-4-1 'union
Select 'Li si', '2014-1-1-1 'union
Select 'Li si', '2017-5-1 'union
Select 'Li si', '2014-9-1 'union
Select 'Li si', '2014-1-1-1 'union
Select 'zhao 6', '2014-1-1-1 'union
Select 'Qian tu', '2014-1-1-1 'union
Select 'qiantu ', '2017-3-1' union
Select 'zhang san', '2017-9-1'
Go
Select custname, DATEDIFF (d, min (saledate), max (saledate)/(COUNT (*)-1) as avgday
From sales
Group by custname
Having count (*)> 1
Go
Select custname, case when count (*)> 1 then DATEDIFF (d, min (saledate), max (saledate)/(COUNT (*)-1)
Else DATEDIFF (d, min (saledate), max (saledate) end
As avgday
From sales
Group by custname
-- Having count (*)> 1
Go
Drop table sales
