How To Sort A Table By The Difference In Percent Between Prices On Different Date

I have this query: SELECT * FROM ( select t.article_number, t.date, t.company, t.size, t.price , count(*) as rnk from price t INNER JOIN price s ON(t.article_number = s.article_num

Solution 1:

• Assuming that sqlite3 uses cte, you can use something like this.
• I used date1 as date and size1 as size as my DB doesn't let me use these keywords.
• The price format should be changed to decimal rather than string.

This is query I tried in Oracle using Common Type Expression. If sqlite doesn't support it, you have to replace the query of each derived table with main query. (like whereever I used tbl1, you have to use the query which is used to derive tbl1 and so on.

with tbl1 as
(select p1.article_number,p1.company,count(p1.date1) as rno,max(p1.date1) as date1,max(p1.size1) as size1,max(p1.price) as price
from price p1 inner join price p2
on p1.article_number=p2.article_number and p1.company=p2.company and p1.date1>=p2.date1
group by p1.article_number,p1.company,p1.date1
)
,tbl2 as
(select p1.article_number,p1.company,count(p1.date1)+1 as rno,max(p1.date1) as date1,max(p1.size1) as size1,max(p1.price) as price
from price p1 inner join price p2
on p1.article_number=p2.article_number and p1.company=p2.company and p1.date1>=p2.date1
group by p1.article_number,p1.company,p1.date1
)
,tbl3 as
(
select tbl1.*,
case when tbl1.price>tbl2.price then ((tbl1.price-tbl2.price)/tbl1.price)*100
else ((tbl2.price-tbl1.price)/tbl1.price)*100 end as diff_percent
from tbl1 inner join tbl2 
on tbl1.article_number=tbl2.article_number and tbl1.company=tbl2.company
and tbl1.rno=tbl2.rno
order by tbl1.article_number,tbl1.company,tbl1.rno
)
,tbl4 as(
select t1.article_number,t1.company,count(t1.diff_percent) as rnk,max(t1.date1) as date1,max(t1.price) as price,max(t1.diff_percent) as diff_percent
from tbl3 t1 inner join tbl3 t2
on t1.article_number=t2.article_number and t1.company=t2.company
and t1.diff_percent <=t2.diff_percent
group by t1.article_number,t1.company,t1.diff_percent
)
select * from tbl4 
where rnk <=2
order by article_number,company,date1