How to write a complex SQL query?

Question

PostgreSQL

Hello colleagues!

Today I faced a difficult task for which I did not have enough skills. I turn to the community for help.
So, there is a history table with something like this structure:
id | task_id | phrase_id | date | position
Some historical data gets into this table - some tasks are performed once or several times a day, which simply write a new position (int) by the phrase (int) and the current time (timestamp) to this table .
The task is to extract summary information in one query: how many phrase positions increased, how many positions sank, how many positions remained unchanged during the last check compared to the previous check for each task for all phrases belonging to this task.
DBMS - postgresql.
Help, pliz, the brain breaks.

Answer 1

Is each check a holistic slice of data? That is, it may be that for phrase 5 the last check was in 2020-07-05, and for phrase 6 it is necessary to rewind in 2020-07-03?
In general, we eat an elephant in parts:

extract summary information: how many phrase positions increased, how many positions sank, how many positions remained unchanged

select count(*) filter(where r1.position < r2.position) as pos_down,
count(*) filter(where r1.position = r2.position) as pos_same,
count(*) filter(where r1.position > r2.position) as pos_up from ...

The second bite: you need to get r1 and r2 from somewhere, respectively, the rows of the table for the compared slices. This is

tablename r1 full join tablename r2 on r1.phrase_id = r2.phrase_id where r1.... and r2....

We finish eating the elephant:
we need to add a filtering condition and get the data of one slice for r1, and another for r2. Here you have not quite specified what is the "previous check". For example, let's get the previous task_id from the tasks table:

where r2.task_id = 123 and r1.task_id in (select id from tasks where id < 123 order by id desc limit 1)

Answer 2

skills here
https://habr.com/ru/company/oleg-bunin/blog/464303/
well, ++ by timescaledb