select date, open, close, abs((open - close)) as diff from ( select A.*, row_number() over (partition by month(A.date) order by abs((A.open - A.close)) desc) as diff_ordered from bitcoin as A ) as B WHERE diff_ordered < 4 order by month(date) asc, diff desc;
select date, open, close, abs((open - close)) as diff from ( select A.*, row_number() over (partition by month(A.date) order by abs((A.open - A.close)) desc) as diff_ordered from bitcoin as A ) as B WHERE diff_ordered < 4 order by month(date) asc, diff desc;
select date, open, close, abs((open - close)) as diff from ( select A.*, row_number() over (partition by month(A.date) order by abs((A.open - A.close)) desc) as diff_ordered from bitcoin as A ) as B WHERE diff_ordered < 4 order by month(date) asc, diff desc;
SELECT date, open, close, diff FROM ( SELECT date, open, close, diff, ROW_NUMBER() OVER (PARTITION BY month ORDER BY diff DESC) AS row FROM ( SELECT date, EXTRACT(MONTH FROM date) AS month, open, close, ABS(close - open) AS diff FROM bitcoin ) b1 ) b2 WHERE b2.row <= 3;
with cte as( select date, month(date) as month, open, close, abs(close - open) as diff, dense_rank() over (partition by month(date) order by abs(close-open) desc) as rank_diff from bitcoin) select date, open, close, diff from cte where rank_diff <= 3 order by month, diff desc;
select t.date, t.diff, t.open, t.close from ( select date, open, close, ABS(close - open) as diff, row_number() over(partition by Month(date) order by ABS(close - open) desc) as rnk from bitcoin order by MONTH(date), diff desc) as t where t.rnk < 4;
select t.date, t.diff, t.open, t.close from ( select date, open, close, ABS(close - open) as diff, row_number() over(partition by Month(date) order by ABS(close - open) desc) as rnk from bitcoin order by MONTH(date), diff desc) as t where t.rnk < 4;
select date, open,close,diff from ( select *,abs(open-close) diff, rank() over (partition by month(date) order by abs(open - close) desc) rn from bitcoin ) a where rn < 4;
select date, open,close,diff from ( select *,abs(open-close) diff, rank() over (partition by month(date) order by abs(open - close) desc) rn from bitcoin ) a where rn < 4;
select date, open,close,diff from ( select *,abs(open-close) diff, rank() over (partition by month(date) order by abs(open - close) desc) rn from bitcoin ) a where rn < 4;