How to select grouped data by date column? [ mysql ]

Question

I have 3 tables, users, user_spents, and user_incomes.

• user_spents and user_incomes tables contains activities of users per date.
• There can be multiple activities in one date per user. (e.g. user can have multiple spent or income in 2022-04-01 date).
• It's not mandatory that user must have spent or income in a given date

I want to select activities of single user grouped and filtered by date.

Sql Fiddle Link and sql What I have tried so far: http://sqlfiddle.com/#!9/846c851/1

What am I doing wrong? Can you give me any suggestions?

Example data what I want to achieve:

name    spent   income  date
----------------------------------
Jack    8       12      2022-04-01
Jack    4       56      2022-04-02
Jack    NULL    44      2022-04-03
Jack    7       NULL    2022-04-04

Sample data. (Alse included in sql fiddle)

Users Table:
id  name
1     Jack

User Spents:
id  user_id spent   date
1     1       2     2022-04-01
2     1       1     2022-04-01
3     1       5     2022-04-01
4     1       3     2022-04-02
5     1       1     2022-04-02

User Spents:
id  user_id income  date
1     1       44        2022-04-03
2     1       15        2022-04-02
3     1       41        2022-04-02
4     1       12        2022-04-01
5     2       54        2022-04-01

Any help will be appreciated. Thanks.

Answer 1

You can use UNION ALL to get all the rows of user_spents and user_incomes and then join to users to aggregate:

SELECT u.id, u.name,
       SUM(t.spent) spent,
       SUM(t.income) income, 
       t.date
FROM users u
INNER JOIN (
  SELECT user_id, spent, null income, date FROM user_spents
  UNION ALL
  SELECT user_id, null, income, date FROM user_incomes
) t ON t.user_id = u.id
WHERE u.name = 'Jack' -- remove this condition to get results for all users
GROUP BY u.id, t.date;

See the demo.