| 1 | FB <- pi_{fu.user_id, u.username, u.email,
|
|---|
| 2 | COALESCE(fu.spending_budget,0)->spending_budget,
|
|---|
| 3 | COALESCE(fu.saving_budget,0)->saving_budget,
|
|---|
| 4 | COALESCE(fu.investing_budget,0)->investing_budget,
|
|---|
| 5 | COALESCE(fu.donation_budget,0)->donation_budget,
|
|---|
| 6 | COALESCE(fu.credit,0)->credit}
|
|---|
| 7 | (finance_users fu bowtie_{fu.user_id = u.user_id} users u)
|
|---|
| 8 |
|
|---|
| 9 | FBM <- FB x M
|
|---|
| 10 | IY <- sigma_{YEAR(i.date)=Y}(incomes i)
|
|---|
| 11 | MI0 <- FBM leftouterjoin_{FBM.user_id = i.user_id AND FBM.month_no = MONTH(i.date)} IY
|
|---|
| 12 | MI <- gamma_{user_id, month_no;
|
|---|
| 13 | SUM(COALESCE(i.amount,0))->month_income}(MI0)
|
|---|
| 14 |
|
|---|
| 15 | MIR <- omega_{PARTITION BY user_id ORDER BY month_income DESC, month_no ASC;
|
|---|
| 16 | DENSE_RANK()->best_month_rank,
|
|---|
| 17 | DENSE_RANK(PARTITION BY user_id ORDER BY month_income ASC, month_no ASC)->worst_month_rank}(MI)
|
|---|
| 18 |
|
|---|
| 19 | AI <- gamma_{user_id;
|
|---|
| 20 | SUM(month_income)->total_income,
|
|---|
| 21 | AVG(month_income)->avg_monthly_income,
|
|---|
| 22 | STDDEV_SAMP(month_income)->income_stddev,
|
|---|
| 23 | MAX(month_income)->best_month_income,
|
|---|
| 24 | MIN(month_income)->worst_month_income,
|
|---|
| 25 | COUNT_IF(month_income>0)->active_income_months}(MI)
|
|---|
| 26 |
|
|---|
| 27 | BWM <- gamma_{user_id;
|
|---|
| 28 | MAX_IF(month_no, best_month_rank=1)->best_month_no,
|
|---|
| 29 | MAX_IF(month_no, worst_month_rank=1)->worst_month_no}(MIR)
|
|---|
| 30 |
|
|---|
| 31 | R0 <- FB bowtie_{FB.user_id=AI.user_id} AI bowtie_{FB.user_id=BWM.user_id} BWM
|
|---|
| 32 | R1 <- alpha_{(spending_budget+saving_budget+investing_budget+donation_budget)*12->planned_annual_budget,
|
|---|
| 33 | total_income->actual_annual_income,
|
|---|
| 34 | income_stddev/NULLIF(avg_monthly_income,0)->income_volatility_cv,
|
|---|
| 35 | total_income-(spending_budget*12)->annual_free_cash_after_spending,
|
|---|
| 36 | (spending_budget*12)/NULLIF(total_income,0)->spending_pressure_ratio,
|
|---|
| 37 | credit/NULLIF(total_income,0)->leverage_ratio}(R0)
|
|---|
| 38 | R <- omega_{ORDER BY annual_free_cash_after_spending DESC,
|
|---|
| 39 | spending_pressure_ratio ASC,
|
|---|
| 40 | user_id ASC;
|
|---|
| 41 | DENSE_RANK()->finance_resilience_rank}(R1)
|
|---|
| 42 |
|
|---|
