I have a table that looks like this:
| child | parent | weight | return |
|---|---|---|---|
| Index1 | Fund1 | 0.7 | -0.08705756 |
| Index2 | Fund1 | 0.3 | -0.08769513 |
| Index3 | Fund2 | 1 | -0.03822276 |
| Fund1 | Fund3 | 0.5 | |
| Fund2 | Fund3 | 0.5 | |
| Index4 | Fund4 | 1 | 0.00166052 |
| Index5 | Fund5 | 0.5 | -0.04069137 |
| Index6 | Fund5 | 0.5 | -0.07492242 |
| Index7 | Fund6 | 0.3 | -0.03460980 |
| Index8 | Fund6 | 0.7 | -0.06578893 |
| Fund6 | Fund7 | 0.65 | |
| Fund8 | Fund7 | 0.15 | |
| Fund9 | Fund7 | 0.2 | |
| Index9 | Fund8 | 1 | -0.10283900 |
| Index10 | Fund9 | 1 | -0.02705685 |
| Index11 | Fund10 | 1 | -0.02842958 |
| Index12 | Fund11 | 1 | -0.00654572 |
| Index13 | Fund12 | 1 | 0.00097750 |
| Fund4 | Fund13 | 0.01 | |
| Fund5 | Fund13 | 0.47 | |
| Fund3 | Fund13 | 0.25 | |
| Fund12 | Fund13 | 0.07 | |
| Fund11 | Fund13 | 0.11 | |
| Fund10 | Fund13 | 0.045 | |
| Fund7 | Fund13 | 0.045 | |
| Fund4 | Fund14 | 0.145 | |
| Fund5 | Fund14 | 0.12 | |
| Fund3 | Fund14 | 0.055 | |
| Fund12 | Fund14 | 0.32 | |
| Fund11 | Fund14 | 0.26 | |
| Fund10 | Fund14 | 0.05 | |
| Fund7 | Fund14 | 0.05 | |
| Fund13 | Fund15 | 0.55 | |
| Fund14 | Fund15 | 0.45 | |
| Fund15 | Fund16 | 1 |
I'm trying to write the sql statement that would take that and output the correct weighted return for each distinct parent (fund):
| parent | weighted_return |
|---|---|
| Fund1 | |
| Fund2 | |
| Fund3 | |
| Fund4 | |
| Fund5 | |
| Fund6 | |
| Fund7 | |
| Fund8 | |
| Fund9 | |
| Fund10 | |
| Fund11 | |
| Fund12 | |
| Fund13 | |
| Fund14 | |
| Fund15 | |
| Fund16 |
As an example: Fund3 is made up 50% from Fund1 and 50% Fund2. Fund1 is made up of 70% Index1 and 30% of Index2. Fund2 is made up 100% of Index3. Fund3 weighted return is 0.5(0.7-0.087 + 0.3-0.088) + 0.5(-0.038)
Really struggling with this one - any guidance would be much appreciated.