users(user_id, referrer_id) and orders(order_id, user_id, revenue)

For every user who was not referred by anybody, return the total revenue from their whole referral subtree, including their own orders. There are no cycles.

Return root_user_id and subtree_revenue, ordered by root_user_id. A root whose tree has no orders shows 0.

The subtree can be any depth, so each root has to be carried down through its descendants rather than joined a fixed number of times.

Tables

orders

order_id  user_id  revenue
--------  -------  -------
1         1        10.00
2         2        20.00
3         3        30.00
4         4        40.00
5         5        50.00

users

user_id  referrer_id
-------  -----------
1        NULL
2        1
3        1
4        2
5        4
6        NULL
7        6

Expected result

root_user_id  subtree_revenue
------------  ---------------
1             150.00
6             0.00