Data for the latest previous effective date sub query Imagine issues with linked

Time entries to track the time spent
Journal entries to track progression evolution

(Actually a part of Redmine schema)
Let's take some sample data:
Issues




id
subject
estimated_hours




1
First issue
4


2
Second issue
8




Time entries




id
issue_id
spent_on
hours




1
1
2020-12-02
1


2
1
2020-12-05
1.5


3
2
2020-12-06
0.5


4
1
2020-12-10
1


5
2
2020-12-10
1


6
2
2020-12-15
2


7
2
2020-12-15
1




Journal




id
issue_id
updated_on
done_ratio




1
1
2020-11-20
0


2
2
2020-11-20
5


3
1
2020-12-01
20


4
1
2020-12-03
40


5
2
2020-12-06
30


6
1
2020-12-07
70


7
1
2020-12-11
100


8
2
2020-12-12
50




Now for each time entry I need to know the latest done ratio.
Here is what I'm expecting




id
subject
spent_on
hours
done_ratio




1
First issue
2020-12-02
1
20


2
First issue
2020-12-05
1.5
40


3
Second issue
2020-12-06
0.5
30


4
First issue
2020-12-10
1
70


5
Second issue
2020-12-10
1
30


6
Second issue
2020-12-15
2
50


7
Second issue
2020-12-15
1
50




I thought I could get that with this query:
SELECT
  tie.id
, iss.subject
, tie.spent_on
, tie.hours
, jou.done_ratio
FROM time_entry AS tie
JOIN issue AS iss ON (tie.issue_id = iss.id)
JOIN (
  SELECT
    issue_id
  , updated_on
  , done_ratio
  FROM journal
  ORDER BY updated_on DESC
) AS jou ON (
  jou.issue_id = tie.issue_id
  AND jou.updated_on &lt;= tie.spent_on
)
GROUP BY tie.id, tie.spent_on
ORDER BY tie.spent_on

But it's returning the first done ratio for every time entry.
Here is a playground to fiddle with the mentioned data
How can I get the done ratio of the latest journal prior to (&lt;=) each time entry ?
I'm using mariadb:10.2.9 here but this should pretty much be standard ANSI SQL
So I'll provide two answers, with the following caveats:

Certain DB engines are bad with correlated subqueries, either in terms of performance or outright lack of support
The row identifier (id) columns carelessly tacked on as a &quot;primary key&quot; will limit performance of both solutions and will likely force a sort, an expensive operation

Given the lack of proper primary keys, the performance may suffer if you are working on a large set of data.  If you're just returning a few hundred/few thousand rows you should be okay on modern hardware.
Methond 1: Correlated Subquery
The most direct way (from a code perspective) is to use a correlated subquery to select the record from journal with the greatest updated_on less than or equal to spent_on from time_entry:
SELECT
  time_entry.id
 ,issue.subject
 ,time_entry.spent_on
 ,time_entry.hours
 ,journal.done_ratio
FROM
  time_entry time_entry 
INNER JOIN
  issue issue
    ON issue.id = time_entry.issue_id
LEFT JOIN
  journal journal
    ON journal.issue_id = time_entry.issue_id
         AND journal.updated_on =
           (
             SELECT
               MAX(updated_on)
             FROM
               journal
             WHERE
               issue_id = time_entry.issue_id
                 AND updated_on &lt;= time_entry.spent_on
           )

Method 2: Window Function
You can also use a windowing function to number the rows according to specified criteria, and selecting only the first row.  This forces a sort in all instances.
SELECT
  id
 ,subject
 ,spent_on
 ,hours
 ,done_ratio
FROM
  (
    SELECT
      time_entry.id
     ,issue.subject
     ,time_entry.spent_on
     ,time_entry.hours
     ,journal.done_ratio
     ,ROW_NUMBER() OVER (PARTITION BY time_entry.id ORDER BY journal.updated_on DESC) AS rownum
    FROM
      time_entry time_entry 
    INNER JOIN
      issue issue
        ON issue.id = time_entry.issue_id
    LEFT JOIN
      journal journal
        ON journal.issue_id = time_entry.issue_id
             AND journal.updated_on  &lt;= time_entry.spent_on
  ) x
WHERE
  rownum = 1