Show number of lower and higher values in SQL

Question

I have a certain problem while trying to make an SQL. I have a table with the following format and data.

id	value	date
12	3	2020-06-01
12	4	2020-06-09
12	1	2020-06-20
5	4	2020-06-11
5	5	2020-06-17

My goal is to make something like that:

id	lower	higher
12	1	1
5	0	1

This looks for the value of the oldest row IN specific interval (ex. 100 days)and it compares it with all dates after that if their values are higher and lower and return the count.

I do have something that works but it requires more queries: One to group take all ids with dates in the interval of xx days

SELECT id FROM table
WHERE date >= CURDATE() - INTERVAL 30 DAY GROUP BY id
ORDER BY id ASC;

And then I loop through each row and get its lower and higher values.

SELECT
*
FROM
(
  SELECT
    COUNT(*) AS higher, id
  FROM
    `table`
  WHERE
      id = 12 AND date > CURDATE() - INTERVAL 30 DAY AND value > (
      SELECT value FROM table 
      WHERE table.date >= CURDATE() - INTERVAL 30 DAY AND id = 12 
      ORDER BY `table`.`date` ASC LIMIT 1
      )
) AS t1,(
  SELECT
    COUNT(*) AS deteriorated_placements
  FROM
    `table`
  WHERE
      id = 12 AND date > CURDATE() - INTERVAL 30 DAY AND value < (
      SELECT value FROM table 
      WHERE table.date >= CURDATE() - INTERVAL 30 DAY AND id = 12 
      ORDER BY `table`.`date` ASC LIMIT 1
    )
) AS t2;

The problem with that is that I do around 40 more queries. I know it maybe is not a big issue but Is there a way to somehow combine those 2 queries?

user_1144035 · Answer

Use first_value():

select id,
       sum(value < value_1) as lower,
       sum(value > value_1) as higher
from (select t.*,
             first_value(value) over (partition by id order by date) as value_1
      from t
     ) t
group by id;

Show number of lower and higher values in SQL

1 Answers