MySQL: aggregate changes for multiple objects from a temporary table

I have a table like this, where every status change for every character is saved.

table characters_changes
+----+--------+---------+-------+----------------------+
| id |  rank  |   job   | money |      datetime        |
+----+--------+---------+-------+----------------------+
| 1  |   2    | tailor  |  25   | 2018-06-01 12:30:15  |
| 1  |   3    |  NULL   |   5   | 2018-06-02 10:50:19  |
| 1  |   2    |  NULL   |  -5   | 2018-06-03 18:44:35  |
| 1  |  NULL  | tinker  |  10   | 2018-06-04 04:10:12  |
| 1  |   3    |  NULL   | NULL  | 2018-06-05 17:31:00  |
|  2 |    1   |   spy   |   7   |  2018-06-01 12:30:15 |
|  2 |    2   |   NULL  |  NULL |  2018-06-02 10:50:19 |
|  2 |   NULL |  no job |   7   |  2018-06-03 17:31:00 |
| 3  |   3    | soldier |  12   | 2018-06-01 12:30:15  |
| 3  |   1    |  NULL   | -11   | 2018-06-02 10:50:19  |
+----+--------+---------+-------+----------------------+

NULL means that there was no change at corresponding attribute. rank and job changes mean replace one with another, while money change means adding and subtracting the sum (if not NULL). There is guaranteed at least one row per character without any NULL-s.

So I need to get a table where I could show current status of every character at the end. With their last rank, last job and resulting sum of money. A table like this one.

table characters_status
+----+--------+---------+-------+
| id |  rank  |   job   | money |
+----+--------+---------+-------+
| 1  |   3    | tinker  |  35   |
|  2 |    2   |  no job |   14  |
| 3  |   1    | soldier |   1   |
+----+--------+---------+-------+

What even worse, table characters_changes is a temporary table. datetime in it comes from another table of events. So as it is temporary, I can only query it once. But there can be any number of characters and most likely going to be more columns like rank and job.

The whole system is needed to provide the possibility to get statuses for all characters at any given datetime by ignoring all changes after that. But that part is easy for me, so I left it out of scope of my question.

Solution

This should work, I think:

SELECT id
   , CASE WHEN INSTR(ranks, '|') = 0 THEN ranks ELSE LEFT(ranks, INSTR(ranks, '|')-1) END AS rank
   , CASE WHEN INSTR(jobs, '|') = 0 THEN jobs ELSE LEFT(jobs, INSTR(jobs, '|')-1) END AS job
   , monies
FROM
(
   SELECT id
      , GROUP_CONCAT(rank ORDER BY datetime DESC SEPARATOR '|') AS ranks
      , GROUP_CONCAT(job ORDER BY datetime DESC SEPARATOR '|') AS jobs
      , SUM(money) AS monies
   FROM characters_changes
   GROUP BY id
) AS lists
;

Technically, you can do it without a subquery, but I broke it down this way for clarity. The alternative would be expressions like this:

, CASE COUNT(rank) WHEN 0 THEN NULL WHEN 1 THEN GROUP_CONCAT(rank ORDER BY datetime DESC SEPARATOR '|') ELSE LEFT(GROUP_CONCAT(rank ORDER BY datetime DESC SEPARATOR '|'), INSTR(GROUP_CONCAT(rank ORDER BY datetime DESC SEPARATOR '|'), '|')-1) END AS rank