MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.30


CREATE TABLE USERS (
    id INT,
    email VARCHAR(255),
    balance DECIMAL(10,2)
);

CREATE TABLE TRANSACTIONS (
    id INT,
    uid INT,
    amount DECIMAL(10,2),
    fees DECIMAL(10,2)
);

CREATE TABLE ORDERS (
    id INT,
    uid INT,
    charge DECIMAL(10,2)
);

INSERT INTO USERS 
  VALUES
(1, 'email1@app.com', 15.50),
(2, 'email2@app.com', 10.00),
(3, 'email3@app.com', 70.00);

INSERT INTO TRANSACTIONS 
  VALUES
(1, 1, 15.50, 0.50),
(2, 2, 10.00, 0.50),
(3, 2, 15.00, 0.50),
(4, 3, 12.50, 0.50),
(5, 1, 5.50, 0.50);

INSERT INTO ORDERS 
  VALUES
(1, 1, 15.50),
(2, 2, 10.50),
(3, 2, 15.00),
(4, 3, 12.50),
(5, 1, 5.50);

Records: 3  Duplicates: 0  Warnings: 0



Records: 5  Duplicates: 0  Warnings: 0



Records: 5  Duplicates: 0  Warnings: 0


SELECT * FROM USERS


id
email
balance


1
email1@app.com
15.50

2
email2@app.com
10.00

3
email3@app.com
70.00


SELECT * FROM TRANSACTIONS


id
uid
amount
fees


1
1
15.50
0.50

2
2
10.00
0.50

3
2
15.00
0.50

4
3
12.50
0.50

5
1
5.50
0.50


SELECT * FROM ORDERS


id
uid
charge


1
1
15.50

2
2
10.50

3
2
15.00

4
3
12.50

5
1
5.50


SELECT a.id AS user_id,
       a.email,
       a.balance, 
       b.total_deposits,
       c.spent
FROM
  USERS a 
  INNER JOIN 
  (SELECT t.uid, 
          SUM(t.amount - t.fees) AS total_deposits
    FROM TRANSACTIONS t
    GROUP BY t.uid) b 
  ON a.id = b.uid
  INNER JOIN
  (SELECT o.uid, 
          SUM(o.charge) AS spent
    FROM ORDERS o
    GROUP BY o.uid) c 
  ON a.id = c.uid
WHERE b.total_deposits < c.spent


user_id
email
balance
total_deposits
spent


1
email1@app.com
15.50
20.00
21.00

2
email2@app.com
10.00
24.00
25.50

3
email3@app.com
70.00
12.00
12.50


WITH b AS 
  (SELECT t.uid, 
          SUM(t.amount - t.fees) AS total_deposits
    FROM TRANSACTIONS t
    GROUP BY t.uid),
  c AS 
  (SELECT o.uid, 
          SUM(o.charge) AS spent
    FROM ORDERS o
    GROUP BY o.uid)
SELECT a.id AS user_id,
       a.email,
       a.balance, 
       b.total_deposits,
       c.spent
FROM
  USERS a 
  INNER JOIN b ON a.id = b.uid
  INNER JOIN c ON a.id = c.uid
WHERE b.total_deposits < c.spent


user_id
email
balance
total_deposits
spent


1
email1@app.com
15.50
20.00
21.00

2
email2@app.com
10.00
24.00
25.50

3
email3@app.com
70.00
12.00
12.50

id	email	balance
1	email1@app.com	15.50
2	email2@app.com	10.00
3	email3@app.com	70.00

user_id	email	balance	total_deposits	spent
1	email1@app.com	15.50	20.00	21.00
2	email2@app.com	10.00	24.00	25.50
3	email3@app.com	70.00	12.00	12.50