By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
select version();
| version |
|---|
| PostgreSQL 13.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit |
CREATE TABLE investors (
id INT,
email TEXT
);
INSERT INTO investors (id, email) VALUES (1, 'john@live.com');
INSERT INTO investors (id, email) VALUES (2, 'mike@live.com');
INSERT INTO investors (id, email) VALUES (3, 'sarah@live.com');
INSERT INTO investors (id, email) VALUES (4, 'dave@live.com');
CREATE TABLE investments (
id INT,
user_id INT,
start_date DATE,
end_date DATE,
amount INT,
percent INT
);
INSERT INTO investments (id, user_id, start_date, end_date, amount, percent) VALUES (1, 1, '2021-01-01', '2021-01-31', 10000, 20);
INSERT INTO investments (id, user_id, start_date, end_date, amount, percent) VALUES (2, 1, '2021-02-01', '2021-02-28', 5000, 20);
INSERT INTO investments (id, user_id, start_date, end_date, amount, percent) VALUES (3, 2, '2021-01-15', '2021-02-15', 20000, 40);
INSERT INTO investments (id, user_id, start_date, end_date, amount, percent) VALUES (4, 3, '2021-02-10', '2021-03-01', 30000, 15);
INSERT INTO investments (id, user_id, start_date, end_date, amount, percent) VALUES (5, 4, '2021-01-25', '2021-02-25', 6000, 45);
CREATE TABLE daily_stats (
id INT,
date DATE,
profit INT
);
INSERT INTO daily_stats (id, date, profit) VALUES (1, '2021-01-01', 254);
INSERT INTO daily_stats (id, date, profit) VALUES (2, '2021-01-02', 188);
INSERT INTO daily_stats (id, date, profit) VALUES (3, '2021-01-03', 361);
INSERT INTO daily_stats (id, date, profit) VALUES (4, '2021-01-04', 469);
INSERT INTO daily_stats (id, date, profit) VALUES (5, '2021-01-05', 194);
INSERT INTO daily_stats (id, date, profit) VALUES (6, '2021-01-06', 155);
INSERT INTO daily_stats (id, date, profit) VALUES (7, '2021-01-07', 322);
INSERT INTO daily_stats (id, date, profit) VALUES (8, '2021-01-08', 354);
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
select
day,
MAX(total_profit) as total_profit,
MAX(total_investment) as total_investment,
MAX(user_investment) as user_investment,
SUM(investment_share) as investment_share,
ROUND(MAX(user_investment) / MAX(total_investment), 4) as user_share,
MAX(total_profit) * SUM(investment_share) * (MAX(user_investment) / MAX(total_investment)) as user_profit
from
(
select
s.date as day,
SUM(i.amount) over (partition by s.date) as user_investment,
SUM(s.profit) as total_profit,
i.amount / (SUM(i.amount) over (partition by s.date)) * (i.percent / 100) as investment_share,
(SUM(s.profit) * (MAX(i.percent) / 100 ) * i.amount / SUM(i.amount) OVER (PARTITION BY s.date)) 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-01-01'::date AND '2021-03-01'::date and user_id IN (1,4)
group by s.date, i.amount, i.percent
order by s.date
) as investment_stats
join
(
select
s.date as day,
SUM(i.amount) as total_investment
from
daily_stats as s
join
investments as i
| day | total_profit | total_investment | user_investment | investment_share | user_share | user_profit |
|---|---|---|---|---|---|---|
| 2021-01-01 | 254 | 10000 | 10000 | 0 | 1.0000 | 0 |
| 2021-01-02 | 188 | 10000 | 10000 | 0 | 1.0000 | 0 |
| 2021-01-03 | 361 | 10000 | 10000 | 0 | 1.0000 | 0 |
| 2021-01-04 | 469 | 10000 | 10000 | 0 | 1.0000 | 0 |
| 2021-01-05 | 194 | 10000 | 10000 | 0 | 1.0000 | 0 |
| 2021-01-06 | 155 | 10000 | 10000 | 0 | 1.0000 | 0 |
| 2021-01-07 | 322 | 10000 | 10000 | 0 | 1.0000 | 0 |
| 2021-01-08 | 354 | 10000 | 10000 | 0 | 1.0000 | 0 |
| 2021-01-09 | 409 | 10000 | 10000 | 0 | 1.0000 | 0 |
| 2021-01-10 | 132 | 10000 | 10000 | 0 | 1.0000 | 0 |
| 2021-01-11 | 172 | 10000 | 10000 | 0 | 1.0000 | 0 |
| 2021-01-12 | 176 | 10000 | 10000 | 0 | 1.0000 | 0 |
| 2021-01-13 | 269 | 10000 | 10000 | 0 | 1.0000 | 0 |
| 2021-01-14 | 195 | 10000 | 10000 | 0 | 1.0000 | 0 |
| 2021-01-15 | 152 | 30000 | 10000 | 0 | 0.0000 | 0 |
| 2021-01-16 | 460 | 30000 | 10000 | 0 | 0.0000 | 0 |
| 2021-01-17 | 423 | 30000 | 10000 | 0 | 0.0000 | 0 |
| 2021-01-18 | 361 | 30000 | 10000 | 0 | 0.0000 | 0 |
| 2021-01-19 | 287 | 30000 | 10000 | 0 | 0.0000 | 0 |
| 2021-01-20 | 320 | 30000 | 10000 | 0 | 0.0000 | 0 |
| 2021-01-21 | 165 | 30000 | 10000 | 0 | 0.0000 | 0 |
| 2021-01-22 | 482 | 30000 | 10000 | 0 | 0.0000 | 0 |
| 2021-01-23 | 142 | 30000 | 10000 | 0 | 0.0000 | 0 |
| 2021-01-24 | 472 | 30000 | 10000 | 0 | 0.0000 | 0 |
| 2021-01-25 | 259 | 36000 | 16000 | 0 | 0.0000 | 0 |
| 2021-01-26 | 325 | 36000 | 16000 | 0 | 0.0000 | 0 |
| 2021-01-27 | 468 | 36000 | 16000 | 0 | 0.0000 | 0 |
| 2021-01-28 | 488 | 36000 | 16000 | 0 | 0.0000 | 0 |
| 2021-01-29 | 480 | 36000 | 16000 | 0 | 0.0000 | 0 |
| 2021-01-30 | 332 | 36000 | 16000 | 0 | 0.0000 | 0 |
| 2021-01-31 | 461 | 36000 | 16000 | 0 | 0.0000 | 0 |
| 2021-02-01 | 248 | 31000 | 11000 | 0 | 0.0000 | 0 |
| 2021-02-02 | 476 | 31000 | 11000 | 0 | 0.0000 | 0 |
| 2021-02-03 | 339 | 31000 | 11000 | 0 | 0.0000 | 0 |
| 2021-02-04 | 464 | 31000 | 11000 | 0 | 0.0000 | 0 |
| 2021-02-05 | 156 | 31000 | 11000 | 0 | 0.0000 | 0 |
| 2021-02-06 | 199 | 31000 | 11000 | 0 | 0.0000 | 0 |
| 2021-02-07 | 359 | 31000 | 11000 | 0 | 0.0000 | 0 |
| 2021-02-08 | 238 | 31000 | 11000 | 0 | 0.0000 | 0 |
| 2021-02-09 | 412 | 31000 | 11000 | 0 | 0.0000 | 0 |
| 2021-02-10 | 303 | 61000 | 11000 | 0 | 0.0000 | 0 |
| 2021-02-11 | 406 | 61000 | 11000 | 0 | 0.0000 | 0 |
| 2021-02-12 | 470 | 61000 | 11000 | 0 | 0.0000 | 0 |
| 2021-02-13 | 404 | 61000 | 11000 | 0 | 0.0000 | 0 |
| 2021-02-14 | 432 | 61000 | 11000 | 0 | 0.0000 | 0 |
| 2021-02-15 | 347 | 61000 | 11000 | 0 | 0.0000 | 0 |
| 2021-02-16 | 296 | 41000 | 11000 | 0 | 0.0000 | 0 |
| 2021-02-17 | 431 | 41000 | 11000 | 0 | 0.0000 | 0 |
| 2021-02-18 | 258 | 41000 | 11000 | 0 | 0.0000 | 0 |
| 2021-02-19 | 217 | 41000 | 11000 | 0 | 0.0000 | 0 |
| 2021-02-20 | 445 | 41000 | 11000 | 0 | 0.0000 | 0 |
| 2021-02-21 | 372 | 41000 | 11000 | 0 | 0.0000 | 0 |
| 2021-02-22 | 492 | 41000 | 11000 | 0 | 0.0000 | 0 |
| 2021-02-23 | 156 | 41000 | 11000 | 0 | 0.0000 | 0 |
| 2021-02-24 | 396 | 41000 | 11000 | 0 | 0.0000 | 0 |
| 2021-02-25 | 220 | 41000 | 11000 | 0 | 0.0000 | 0 |
| 2021-02-26 | 477 | 35000 | 5000 | 0 | 0.0000 | 0 |
| 2021-02-27 | 116 | 35000 | 5000 | 0 | 0.0000 | 0 |
| 2021-02-28 | 355 | 35000 | 5000 | 0 | 0.0000 | 0 |
-- user_share (i.amount / (SUM(i.amount) over (partition by s.date))) * i.percent
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
, ROUND((1.0 * i.amount / (SUM(i.amount) over (partition by s.date))) * i.percent, 2) AS user_share
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'
;
| date | profit | user_id | amount | percent | total_inv | user_profit | user_share |
|---|---|---|---|---|---|---|---|
| 2021-02-01 | 248 | 1 | 5000 | 20 | 31000 | 8.00 | 3.23 |
| 2021-02-01 | 248 | 4 | 6000 | 45 | 31000 | 21.60 | 8.71 |
| 2021-02-01 | 248 | 2 | 20000 | 40 | 31000 | 64.00 | 25.81 |
| 2021-02-02 | 476 | 2 | 20000 | 40 | 31000 | 122.84 | 25.81 |
| 2021-02-02 | 476 | 1 | 5000 | 20 | 31000 | 15.35 | 3.23 |
| 2021-02-02 | 476 | 4 | 6000 | 45 | 31000 | 41.46 | 8.71 |
| 2021-02-03 | 339 | 2 | 20000 | 40 | 31000 | 87.48 | 25.81 |
| 2021-02-03 | 339 | 1 | 5000 | 20 | 31000 | 10.94 | 3.23 |
| 2021-02-03 | 339 | 4 | 6000 | 45 | 31000 | 29.53 | 8.71 |
| 2021-02-04 | 464 | 1 | 5000 | 20 | 31000 | 14.97 | 3.23 |
| 2021-02-04 | 464 | 4 | 6000 | 45 | 31000 | 40.41 | 8.71 |
| 2021-02-04 | 464 | 2 | 20000 | 40 | 31000 | 119.74 | 25.81 |
| 2021-02-05 | 156 | 2 | 20000 | 40 | 31000 | 40.26 | 25.81 |
| 2021-02-05 | 156 | 1 | 5000 | 20 | 31000 | 5.03 | 3.23 |
| 2021-02-05 | 156 | 4 | 6000 | 45 | 31000 | 13.59 | 8.71 |
WITH query1 AS (
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
, ROUND((1.0 * i.amount / (SUM(i.amount) over (partition by s.date))) * i.percent, 2) AS user_share
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'
)
SELECT date
, MAX(profit) AS profit
, MAX(total_inv) AS total_inv
, SUM(user_profit) AS total_profit
, SUM(user_share) AS total_share
FROM query1
WHERE user_id IN (1, 4)
GROUP BY date
;
| date | profit | total_inv | total_profit | total_share |
|---|---|---|---|---|
| 2021-02-01 | 248 | 31000 | 29.60 | 11.94 |
| 2021-02-02 | 476 | 31000 | 56.81 | 11.94 |
| 2021-02-03 | 339 | 31000 | 40.47 | 11.94 |
| 2021-02-04 | 464 | 31000 | 55.38 | 11.94 |
| 2021-02-05 | 156 | 31000 | 18.62 | 11.94 |