Postgresql: calculate share of total investment per investor, per day

Solution for Postgresql: calculate share of total investment per investor, per day
is Given Below:

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: https://www.db-fiddle.com/f/m1DFJKzMs8SuYeVAUYcHU9/6

investors is basically my “users” table. It contains investor details such as email, name etc.

In the investments table, I’m storing data for each of the investors’ investments. The start_date is when the investment begins, end_date is when it ends, amount is how much is invested, and percent is the share of the profit that the investor receives on his investment. (the rest being taken as a fee).

And in my daily_stats table, I have the total daily profit on the sum of all the investments that are active for each day.

Now, the tricky part is producing a dataset for each investor which shows them how much daily profit they’ve made on their individual investment from the data provided. for example:

date profit
2021-01-01 $3
2021-01-02 $7
2021-01-03 $1
2021-01-04 $5
2021-01-05 $9

For this, my query needs to calculate the investor’s share of the total investment for each daily stat, and also apply their investment percentage onto the profit. For example, if you take a look at Mike’s investment, you’ll see that it intersects the two other investments which belong to John. We can visualise it like this:

enter image description here

So each investors’ share of the total investment changes depending on how many investments there are and what their sizes are. Let’s take a look at a single date: 2021-02-03

In daily_stats, the profit for that date is $339

the total investment on that date is $25000

John’s share is $5000 (20%), and his take profit percentage is 20.

Mike’s share is $20000 (80%), and his take profit percentage 40.

Therefore Mike walks home with 339 * 0.8 * 0.4 = 108.48

And John takes 339 * 0.2 * 0.2 = 13.56

Now I’m thinking I can set up a generate_series for the time period that I want to select stats for and JOIN the investments/daily_stats tables but I’m not sure how to set it up such that the investor’s profit is being calculated per day as demonstrated

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'
 ORDER BY 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 |
+------------+--------+---------+--------+---------+-----------+-------------+