How to do the complex nested select SQL?
I have tested this query:

SELECT budget.aid,
       sum(CASE change_type.table2_id WHEN 1 THEN 1 ELSE 0 END) as login,
       sum(CASE change_type.table2_id WHEN 3 THEN 1 ELSE 0 END) as search,
       CASE budget.table2_id WHEN 2 THEN IF(LEFT(budget.message, 8) = 'increase', 1, -1) ELSE 0 END as budget
FROM table1 budget  
LEFT JOIN table1 change_type 
ON budget.aid = change_type.aid 
AND change_type.date BETWEEN budget.date - INTERVAL 1 DAY AND budget.date
AND change_type.table2_id &lt;&gt; 2
WHERE budget.table2_id = 2
GROUP BY budget.aid, budget.date;


It was a fun query, I made it with joins, maybe the subselect version could be easier to understand. I perform SELF join to match budgets with other actions in the range requested. Please note that it filters other budget actions and that you will get an overlap if at least 2 budget actions are performed within 1 day.

To fill the second and third column, a sum of a CASE is performed after the grouping by aid and the reference date.

This is the result:

+-----+-------+--------+--------+
| aid | login | search | budget |
+-----+-------+--------+--------+
| 121 |     0 |      0 |      1 |
| 121 |     0 |      2 |      1 |
| 123 |     0 |      0 |     -1 |
+-----+-------+--------+--------+
3 rows in set (0.00 sec)


Please note that no login for the aid 123 so this query returns the right results. There is a mistake on your example. Alternatively, if you do not want to have into account the aid for the actions, then the result would be:

+-----+-------+--------+--------+
| aid | login | search | budget |
+-----+-------+--------+--------+
| 121 |     0 |      0 |      1 |
| 121 |     0 |      2 |      1 |
| 123 |     1 |      0 |     -1 |
+-----+-------+--------+--------+
3 rows in set (0.00 sec)


To obtain this, remove the budget.aid = change_type.aid AND from my query.

Also please note that no query has been done on table2, so I assume values are hardcoded and do not change. You can expand the query to join the other table if that is not correct.