Query on comparing products

Transact-SQL
22

I see bitsMed used join on rownumber
Instead of date

I know what that means..

:sunglasses::sunglasses::sunglasses::sunglasses:

23

Is it possible to alter the CTE with these below data and structure ? I would not have difference and the sign columns ??

create table #fruit(
product varchar(10)
,price decimal(10,2)
,[date] date

);

insert into fruit values
('Apple',10,'2018-08-01')
,('Apple',9,'2018-08-02')
,('Apple',10,'2018-08-03')
,('Apple',8.5,'2018-08-07')
,('Apple',10,'2018-08-08')
,('Orange',4,'2018-08-01')
,('Orange',4.5,'2018-08-02')
,('Orange',3.5,'2018-08-03')
,('Orange',6,'2018-08-07')
,('Orange',5.2,'2018-08-08')
,('Mango',6.5,'2018-08-01')
,('Mango',7,'2018-08-02')
,('Mango',8.4,'2018-08-03')
,('Mango',6.5,'2018-08-07')
,('Mango',5.5,'2018-08-05')
;

24

For this specific scenario, try this:

with cte1
  as (select product
            ,price
            ,[date] as dt
            ,count(*) over(partition by product) as n
            ,row_number() over(partition by product order by [date]) as rn
        from #fruit
     )
    ,cte2
  as (select a.product
            ,a.dt
            ,a.n
            ,a.rn
            ,sign(a.price-b.price) as s
        from cte1 as a
             left outer join cte1 as b
                          on b.product=a.product
                         and b.rn=a.rn-1
     )
select a.product
      ,b.product
      ,avg(a.[n]) as [base total count]
      ,count(*) as [matched count]
  from cte2 as a
       inner join cte2 as b
               on b.product!=a.product
              and b.rn=a.rn+1
              and b.s=a.s
 group by a.product
         ,b.product
 order by a.product
         ,[matched count] desc
;

Come to this of it, this only works if you have rows for same dates, for each fruit. Maybe a better solution would be using lag/lead :thinking: