Postgresql: Calculate Share Of Total Investment Per Investor, Per Day

Notice: I am using a Y-m-d date format throughout this post. So I have a database containing three tables: investors, investments, daily_stats I've set them up at this db-fiddle: h

Solution 1:

The following can be applied to any range of dates, for all users at once. In this specific SQL, I've calculated the user_profit between the dates 2021-02-01 to 2021-02-05.

Note also that I believe your calculation for John's share is $5000 (25%) is incorrect. $5000 of $25000 is 20%, not 25%. The same issue applies to Mike's share. Due to that, the following results do not match your expected results exactly. But I believe this SQL is correct.

SELECT s.date, s.profit
     , i.user_id, i.amount, i.percent
     , SUM(i.amount) OVER (PARTITION BY s.date) AS total_inv
     , ROUND(s.profit * (i.percent / 100.0) * i.amount / SUM(i.amount) OVER (PARTITION BY s.date), 2) AS user_profit
  FROM daily_stats AS s
  JOIN investments AS i
    ON s.date BETWEEN i.start_date AND i.end_date
 WHERE s.date BETWEEN '2021-02-01' AND '2021-02-05'ORDERBY s.date, i.user_id
;

+------------+--------+---------+--------+---------+-----------+-------------+
| date       | profit | user_id | amount | percent | total_inv | user_profit |
+------------+--------+---------+--------+---------+-----------+-------------+
| 2021-02-01 |    248 |       1 |   5000 |      20 |     25000 |        9.92 |
| 2021-02-01 |    248 |       2 |  20000 |      40 |     25000 |       79.36 |
| 2021-02-02 |    476 |       1 |   5000 |      20 |     25000 |       19.04 |
| 2021-02-02 |    476 |       2 |  20000 |      40 |     25000 |      152.32 |
| 2021-02-03 |    339 |       1 |   5000 |      20 |     25000 |       13.56 |
| 2021-02-03 |    339 |       2 |  20000 |      40 |     25000 |      108.48 |
| 2021-02-04 |    464 |       1 |   5000 |      20 |     25000 |       18.56 |
| 2021-02-04 |    464 |       2 |  20000 |      40 |     25000 |      148.48 |
| 2021-02-05 |    156 |       1 |   5000 |      20 |     25000 |        6.24 |
| 2021-02-05 |    156 |       2 |  20000 |      40 |     25000 |       49.92 |
+------------+--------+---------+--------+---------+-----------+-------------+

I've updated your fiddle as well: Solution in your fiddle

Here's the result that includes dates in January as well, which shows a different total_investment for those dates:

+------------+--------+---------+--------+---------+-----------+-------------+|date|profit|user_id|amount|percent|total_inv|user_profit|+------------+--------+---------+--------+---------+-----------+-------------+|2021-01-28|488|1|10000|20|30000|32.53||2021-01-28|488|2|20000|40|30000|130.13||2021-01-29|480|1|10000|20|30000|32.00||2021-01-29|480|2|20000|40|30000|128.00||2021-01-30|332|1|10000|20|30000|22.13||2021-01-30|332|2|20000|40|30000|88.53||2021-01-31|461|1|10000|20|30000|30.73||2021-01-31|461|2|20000|40|30000|122.93||2021-02-01|248|1|5000|20|25000|9.92||2021-02-01|248|2|20000|40|25000|79.36||2021-02-02|476|1|5000|20|25000|19.04||2021-02-02|476|2|20000|40|25000|152.32||2021-02-03|339|1|5000|20|25000|13.56||2021-02-03|339|2|20000|40|25000|108.48||2021-02-04|464|1|5000|20|25000|18.56||2021-02-04|464|2|20000|40|25000|148.48||2021-02-05|156|1|5000|20|25000|6.24||2021-02-05|156|2|20000|40|25000|49.92|+------------+--------+---------+--------+---------+-----------+-------------+