How to find out the history of data changes using SQL query?

Question

SQL

Hello.
There are two tables
CREATE TABLE links ([id] INTEGER PRIMARY KEY AUTOINCREMENT, [link_id] CHAR);
and
CREATE TABLE history_yap([id] INTEGER PRIMARY KEY AUTOINCREMENT, [link_id] INTEGER, [time] INTEGER, [value] INTEGER);
The first table stores the data, the second - the history of changes in certain parameters related to this data.
How can one query determine if there has been a data change?
For example:
in table 1 data
id=1
link_Id=123
in table 2 data:
1 123 1390444262 0
2 123 1390444263
1 the change was (at time 1390444262 it was 0, and at time 1390444263 it became 1).
Relevant for SqLite.
Thank you.

Answer 1

SELECT t.id, COUNT(*), SUM(t.ct)
FROM (SELECT l.id, y.value, COUNT(*) as ct
    FROM links l
    INNER JOIN history_yap y ON l.link_id = y.link_id
    GROUP BY l.id, y.value) t
GROUP BY t.id
UNION ALL
SELECT l.id, -1, -1
FROM links l
LEFT JOIN history_yap y ON l.link_id = y.link_id
WHERE y.id IS NULL;

The logic is almost the same, but in the inner query we also count how many records there were in links X history_yap. Also added a second request for links without any entries in history_yap. As a result, we have a table with three columns:
* link.id - the desired
* number of unique values. If -1, then there were no entries in history_yap for this links.id. If greater than 1, then there are at least two history_yap for this link with different values. If 1, then look at the third column.
* number of lines in links X history_yap for each links.id. If the second column is 1 and the third column is 1, then there is only one history_yap entry for this links.id. All other values ​​are ignored.
PS replied with a new post, tk. Do not add code in comments.

Answer 2

SELECT t.id, COUNT(*)
FROM (SELECT l.id, y.value
    FROM links l
    INNER JOIN history_yap y ON l.link_id = y.link_id
    GROUP BY l.id, y.value) t
GROUP BY t.id
HAVING COUNT(*) > 1;

Indexes on link_id are required for fast work. Logic:
* select all links and their history of changes, we are interested in unique values. This inner select can be augmented with where to select rows only from a certain time.
* the resulting table is again grouped by id, we get a link and the number of unique values.
* add the condition "if there are more than 1 unique values", which means there was a change.
The query only sees changes from one value to another. The moments when the link was added to links, as well as the first history entry in history_yap, the request does not see.