Back

Answered by battlethechamp

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;

Answered by ran428

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;

Answered by trabi71

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;

Answered by aaaaaaaaaaaaaaaaa

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;

Answered by stuartwilsonb

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;

Answered by sbw0125

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;

Answered by diddyp20

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;

Answered by jrz1977

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;

Answered by parth

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;

Answered by anonymousUser

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;