# Finding time statistics of item sold by a specific seller

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

1. 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..

2. 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...

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

hi

i tried to do this ...hope it helps
i love any feedback ..!!!!!!

drop create data ...
``````drop table #input2
go

drop table #output
go

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')
go

select 'data',*from #input2
order by ts
go

select 'expected output',*from #output
go
``````
SQL ...
``````; WITH cte_rn
AS (SELECT Row_number()
OVER(
partition BY item
ORDER BY ts ) AS rn,
*
FROM   #input2),
cte_next
AS (SELECT *
FROM   cte_rn
WHERE  rn = 1
UNION ALL
SELECT *
FROM   cte_rn
WHERE  sellerid = 'dfjk'),
cte_next1
AS (SELECT Row_number()
OVER(
partition BY item
ORDER BY ts ) AS rn,
ts,
item,
sellerid
FROM   cte_next),
cte_final
AS (SELECT Datediff(day, a.ts, b.ts) AS diffdays,
a.item
FROM   cte_next1 a
JOIN cte_next1 b
ON a.item = b.item
AND a.rn + 1 = b.rn
WHERE  Datediff(day, a.ts, b.ts) <> 0)
SELECT 'my output',
item,
Avg(diffdays) AS averagetimeofappearence,
Min(diffdays) AS minimumdays,
Max(diffdays) AS maximumdays
FROM   cte_final
GROUP  BY item
ORDER  BY item DESC

go
``````

``````SELECT i.item AS asin,
MAX(DATEDIFF(DAY, i_item.ts_min, i.ts)) /
SUM(CASE WHEN i_item.ts_min = i.ts THEN 0 ELSE 1 END) AS avg_days,
MIN(CASE WHEN i_item.ts_min = i.ts THEN 99999 ELSE
DATEDIFF(DAY, COALESCE(i.previous_ts, CASE WHEN i_item.ts_min = i.ts
THEN i_item.ts_min_diff_seller ELSE i_item.ts_min END), i.ts) END) AS min_days,
/*not sure about the "- 1", remove it if you think the total without it is correct */
MAX(DATEDIFF(DAY, COALESCE(i.previous_ts, i_item.ts_max), i.ts) - 1) AS max_days
FROM (
SELECT *, LAG(ts) OVER(PARTITION BY item ORDER BY ts) AS previous_ts
FROM #input2
WHERE sellerid = 'dfjk'
) AS i
INNER JOIN (
SELECT item,
MIN(ts) AS ts_min,
MIN(CASE WHEN sellerid <> 'dfjk' THEN ts END) AS ts_min_diff_seller,
MAX(ts) AS ts_max
FROM #input2
GROUP BY item
) AS i_item ON i_item.item = i.item
GROUP BY i.item``````

Hi Harish, Output was the table that I was expecting...

Hi

53 45

Mine 54 46 ..slight difference

Hi Harish,

Sorry, what I meant was I was not able to see your query there...

Thanks,
Arun

drop table #input2
go

drop table #output
go

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')
go

select 'data',*from #input2
order by ts
go

select 'expected output',*from #output
go

SQL ..

; WITH cte_rn
AS (SELECT Row_number()
OVER(
partition BY item
ORDER BY ts ) AS rn,
*
FROM #input2),
cte_next
AS (SELECT *
FROM cte_rn
WHERE rn = 1
UNION ALL
SELECT *
FROM cte_rn
WHERE sellerid = 'dfjk'),
cte_next1
AS (SELECT Row_number()
OVER(
partition BY item
ORDER BY ts ) AS rn,
ts,
item,
sellerid
FROM cte_next),
cte_final
AS (SELECT Datediff(day, a.ts, b.ts) AS diffdays,
a.item
FROM cte_next1 a
JOIN cte_next1 b
ON a.item = b.item
AND a.rn + 1 = b.rn
WHERE Datediff(day, a.ts, b.ts) <> 0)
SELECT 'my output',
item,
Avg(diffdays) AS averagetimeofappearence,
Min(diffdays) AS minimumdays,
Max(diffdays) AS maximumdays
FROM cte_final
GROUP BY item
ORDER BY item DESC

go

@harishgg1

That's why I had this comment in my original code:
/*not sure about the "- 1", remove it if you think the total without it is correct */

I subtracted one to match the output originally requested, but I wasn't sure that was correct.

Scott ... thanks for pointing it out ....

I saw what you did !!!

i did not go into details of why my output was not matching Original Poster's output
i did not bring out the screw driver and get into the nuts and bolts !!!

i was waiting for some sort of communciation from the original poster
to resolve it !!!