Finding time statistics of item sold by a specific seller

arunc · September 4, 2019, 6:40am

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

harishgg1 · September 4, 2019, 2:21pm

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

ScottPletcher · September 4, 2019, 7:54pm

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

arunc · September 12, 2019, 6:04am

Thanks Scott!..Appreciate your help

arunc · September 12, 2019, 6:05am

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

harishgg1 · September 12, 2019, 6:53am

Hi

Your output

53 45

Mine 54 46 ..slight difference

arunc · September 12, 2019, 7:00am

Hi Harish,

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

Thanks,
Arun

harishgg1 · September 12, 2019, 7:53am

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

ScottPletcher · September 12, 2019, 2:31pm

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

harishgg1 · September 12, 2019, 2:46pm

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 !!!