Hi All,
Hope you are doing well!...I am trying to understand the average timegap between when an item is sold by a seller and also the minimum and maximum timegap between when a seller goes out and again comes back to sell the item..
The seller that I am focusing on my ddl below is 'dfjk' and the below is the database of items sold by sellers on specific dates
-
if 'dfjk' sells the item on the earliest day for the item then the time gap between the subsequent appearance days should be considered for calculating the average appearance and the maximum, minimum days timegap..
-
If 'dfjk' does not sell the item on the earliest day then the subsequent appearance days should be taken into account while calculating the averageapperancedays and the minimum ,maximum days time gap..
For example in my DDL below item b123 is sold by dfjk first 4 days after the item was introduced on june 28th and the second time dfjk sold the item was after 53 days on Aug 25th and the third time dfjk sold the item was on sep 5th that is 11 days after the second time the item was sold by dfjk..
So the average is (4+53+11)/3=23 days and the minimum is 4 days and maximum is 53 days
For the second item a123 the item was sold by dfjk at the earliest ..so that needs to be excluded ..the next time a123 was sold by dfjk was 15 days after the earliest date and there after for the 3rd time 45 days after the second time a123 was sold....
So the average is =(15+45)/2=30 days and the minimum days is 15 and the maximum days is 45...
Can you please help here...
create table #input2
(ts datetime2,
item varchar(20),
sellerid varchar(20)
)
insert into #input2
values
('2019-09-05 12:02:55.533','b123 ','dfjk'),
('2019-09-03 12:02:55.533','b123 ','TV12'),
('2019-09-03 12:02:55.533','b123 ','uiop'),
('2019-09-03 12:02:55.533','b123 ','klop'),
('2019-08-25 12:02:55.533','b123','hjkl'),
('2019-08-25 12:02:55.533','b123','dfjk'),
('2019-08-25 12:02:55.533','b123','vbn'),
('2019-08-01 12:02:55.533','b123','hjkl'),
('2019-07-02 12:02:55.533','b123','dfjk'),
('2019-06-28 12:02:55.533','b123','cvba'),
('2019-06-28 12:02:55.533','a123','nmde'),
('2019-06-20 12:02:55.533','a123','dfjk'),
('2019-06-10 12:02:55.533','a123','jklo'),
('2019-06-10 12:02:55.533','a123','opio'),
('2019-05-15 12:02:55.533','a123','uiop'),
('2019-05-05 12:02:55.533','a123','dfjk'),
('2019-04-25 12:02:55.533','a123','hjkl'),
('2019-04-20 12:02:55.533','a123','dfjk')
create table #output
(asin varchar(20),
averagetimeofappearence int,
minimumdays int,
maximumdays int
)
insert into #output values
('b123','23','4','53'),
('a123','30','15','45')
Thanks,
Arun