Index: db-scripts/p6-relational-algebra/p6_q1_annual_finance_budget_resilience_ra.txt =================================================================== --- db-scripts/p6-relational-algebra/p6_q1_annual_finance_budget_resilience_ra.txt (revision 89156c1feb4b06a2d4ecb2bbac89f7c39f0df7e2) +++ db-scripts/p6-relational-algebra/p6_q1_annual_finance_budget_resilience_ra.txt (revision 89156c1feb4b06a2d4ecb2bbac89f7c39f0df7e2) @@ -0,0 +1,42 @@ +FB <- pi_{fu.user_id, u.username, u.email, + COALESCE(fu.spending_budget,0)->spending_budget, + COALESCE(fu.saving_budget,0)->saving_budget, + COALESCE(fu.investing_budget,0)->investing_budget, + COALESCE(fu.donation_budget,0)->donation_budget, + COALESCE(fu.credit,0)->credit} + (finance_users fu bowtie_{fu.user_id = u.user_id} users u) + +FBM <- FB x M +IY <- sigma_{YEAR(i.date)=Y}(incomes i) +MI0 <- FBM leftouterjoin_{FBM.user_id = i.user_id AND FBM.month_no = MONTH(i.date)} IY +MI <- gamma_{user_id, month_no; + SUM(COALESCE(i.amount,0))->month_income}(MI0) + +MIR <- omega_{PARTITION BY user_id ORDER BY month_income DESC, month_no ASC; + DENSE_RANK()->best_month_rank, + DENSE_RANK(PARTITION BY user_id ORDER BY month_income ASC, month_no ASC)->worst_month_rank}(MI) + +AI <- gamma_{user_id; + SUM(month_income)->total_income, + AVG(month_income)->avg_monthly_income, + STDDEV_SAMP(month_income)->income_stddev, + MAX(month_income)->best_month_income, + MIN(month_income)->worst_month_income, + COUNT_IF(month_income>0)->active_income_months}(MI) + +BWM <- gamma_{user_id; + MAX_IF(month_no, best_month_rank=1)->best_month_no, + MAX_IF(month_no, worst_month_rank=1)->worst_month_no}(MIR) + +R0 <- FB bowtie_{FB.user_id=AI.user_id} AI bowtie_{FB.user_id=BWM.user_id} BWM +R1 <- alpha_{(spending_budget+saving_budget+investing_budget+donation_budget)*12->planned_annual_budget, + total_income->actual_annual_income, + income_stddev/NULLIF(avg_monthly_income,0)->income_volatility_cv, + total_income-(spending_budget*12)->annual_free_cash_after_spending, + (spending_budget*12)/NULLIF(total_income,0)->spending_pressure_ratio, + credit/NULLIF(total_income,0)->leverage_ratio}(R0) +R <- omega_{ORDER BY annual_free_cash_after_spending DESC, + spending_pressure_ratio ASC, + user_id ASC; + DENSE_RANK()->finance_resilience_rank}(R1) + Index: db-scripts/p6-relational-algebra/p6_q2_annual_training_consistency_workload_ra.txt =================================================================== --- db-scripts/p6-relational-algebra/p6_q2_annual_training_consistency_workload_ra.txt (revision 89156c1feb4b06a2d4ecb2bbac89f7c39f0df7e2) +++ db-scripts/p6-relational-algebra/p6_q2_annual_training_consistency_workload_ra.txt (revision 89156c1feb4b06a2d4ecb2bbac89f7c39f0df7e2) @@ -0,0 +1,52 @@ +TB <- pi_{tu.user_id, u.username, u.email, tu.gender, tu.age, tu.weight} + (training_users tu bowtie_{tu.user_id = u.user_id} users u) + +TBM <- TB x M +TSY <- sigma_{YEAR(ts.date)=Y}(training_sessions ts) +MS0 <- TBM leftouterjoin_{TBM.user_id = ts.training_user_id AND TBM.month_no = MONTH(ts.date)} TSY +MS <- gamma_{user_id, month_no; + COUNT(ts.training_id)->sessions_count, + SUM(COALESCE(ts.duration,0))->total_duration_minutes, + SUM(COALESCE(ts.calories,0))->total_calories, + AVG(COALESCE(ts.duration,0))->avg_session_duration, + AVG(COALESCE(ts.calories,0))->avg_session_calories}(MS0) + +MR <- omega_{PARTITION BY user_id ORDER BY total_calories DESC, month_no ASC; + DENSE_RANK()->peak_calorie_month_rank, + DENSE_RANK(PARTITION BY user_id ORDER BY sessions_count DESC, month_no ASC)->peak_sessions_month_rank}(MS) + +AMS <- sigma_{sessions_count>0}(MS) +AMS1 <- omega_{PARTITION BY user_id ORDER BY month_no; + ROW_NUMBER()->rn}(AMS) +AMS2 <- alpha_{month_no - rn -> grp}(AMS1) +LS0 <- gamma_{user_id, grp; COUNT(*)->streak_len}(AMS2) +LS <- gamma_{user_id; MAX(streak_len)->longest_active_month_streak}(LS0) + +AT <- gamma_{user_id; + SUM(sessions_count)->annual_sessions, + SUM(total_duration_minutes)->annual_duration_minutes, + SUM(total_calories)->annual_calories, + AVG(total_duration_minutes)->avg_monthly_duration, + AVG(total_calories)->avg_monthly_calories, + COUNT_IF(sessions_count>0)->active_months, + REGR_SLOPE(total_calories, month_no)->calories_trend_slope, + REGR_SLOPE(total_duration_minutes, month_no)->duration_trend_slope}(MS) + +PM <- gamma_{user_id; + MAX_IF(month_no, peak_calorie_month_rank=1)->peak_calorie_month_no, + MAX_IF(month_no, peak_sessions_month_rank=1)->peak_sessions_month_no}(MR) + +R0 <- TB bowtie_{TB.user_id=AT.user_id} AT + bowtie_{TB.user_id=PM.user_id} PM + leftouterjoin_{TB.user_id=LS.user_id} LS +R1 <- alpha_{active_months/12.0->consistency_ratio, + COALESCE(longest_active_month_streak,0)->longest_active_month_streak_nz, + COALESCE(calories_trend_slope,0)->calories_trend_slope_nz, + COALESCE(duration_trend_slope,0)->duration_trend_slope_nz}(R0) +R <- omega_{ORDER BY annual_calories DESC, + active_months DESC, + longest_active_month_streak_nz DESC, + user_id ASC; + DENSE_RANK()->training_annual_rank}(R1) + + Index: db-scripts/p6-relational-algebra/p6_q3_annual_discipline_adherence_streaks_ra.txt =================================================================== --- db-scripts/p6-relational-algebra/p6_q3_annual_discipline_adherence_streaks_ra.txt (revision 89156c1feb4b06a2d4ecb2bbac89f7c39f0df7e2) +++ db-scripts/p6-relational-algebra/p6_q3_annual_discipline_adherence_streaks_ra.txt (revision 89156c1feb4b06a2d4ecb2bbac89f7c39f0df7e2) @@ -0,0 +1,56 @@ +DB <- pi_{du.user_id, u.username, u.email} + (discipline_users du bowtie_{du.user_id = u.user_id} users u) + +TC <- tasks t leftouterjoin_{t.custom_tracking_id = c.custom_tracking_id} custom_tracking_categories c +TM0 <- alpha_{COALESCE(t.discipline_user_id, c.user_id)->owner_user_id}(TC) +TM1 <- sigma_{t.discipline_user_id IS NOT NULL OR t.custom_tracking_id IS NOT NULL}(TM0) +TM <- gamma_{owner_user_id; + COUNT(*)->total_tasks_defined, + COUNT_IF(t.custom_tracking_id IS NULL)->core_tasks, + COUNT_IF(t.custom_tracking_id IS NOT NULL)->custom_tasks, + COUNT_DISTINCT(COALESCE(t.custom_tracking_id,'core'))->task_category_span}(TM1) + +ADC0 <- sigma_{YEAR(dc.date)=Y}(daily_completion dc) +ADC <- alpha_{COALESCE(dc.procent,0)->procent, + CASE(procent>=80,1,0)->strong_day}(ADC0) + +DCS <- gamma_{user_id; + COUNT(*)->tracked_days, + AVG(procent)->avg_completion_percent, + PERCENTILE_CONT_0_5(procent)->median_completion_percent, + COUNT_IF(procent=100)->perfect_days, + COUNT_IF(procent>=80)->strong_days, + STDDEV_SAMP(procent)->completion_variability}(ADC) + +SDS0 <- sigma_{strong_day=1}(ADC) +SDS1 <- omega_{PARTITION BY user_id ORDER BY date; ROW_NUMBER()->rn}(SDS0) +SDS2 <- alpha_{date - rn -> grp}(SDS1) +LSS0 <- gamma_{user_id, grp; COUNT(*)->streak_len}(SDS2) +LSS <- gamma_{user_id; MAX(streak_len)->longest_strong_day_streak}(LSS0) + +ATE0 <- ADC0 leftouterjoin_{ADC0.daily_completion_id = tdc.daily_completion_id} task_daily_completion tdc +ATE <- gamma_{ADC0.user_id; COUNT(tdc.task_id)->completed_task_events}(ATE0) + +R0 <- DB + leftouterjoin_{DB.user_id = TM.owner_user_id} TM + leftouterjoin_{DB.user_id = DCS.user_id} DCS + leftouterjoin_{DB.user_id = ATE.user_id} ATE + leftouterjoin_{DB.user_id = LSS.user_id} LSS +R1 <- alpha_{COALESCE(total_tasks_defined,0)->total_tasks_defined_nz, + COALESCE(core_tasks,0)->core_tasks_nz, + COALESCE(custom_tasks,0)->custom_tasks_nz, + COALESCE(task_category_span,0)->task_category_span_nz, + COALESCE(tracked_days,0)->tracked_days_nz, + COALESCE(avg_completion_percent,0)->avg_completion_percent_nz, + COALESCE(median_completion_percent,0)->median_completion_percent_nz, + COALESCE(perfect_days,0)->perfect_days_nz, + COALESCE(strong_days,0)->strong_days_nz, + COALESCE(completion_variability,0)->completion_variability_nz, + COALESCE(completed_task_events,0)->completed_task_events_nz, + COALESCE(longest_strong_day_streak,0)->longest_strong_day_streak_nz, + COALESCE(strong_days/NULLIF(tracked_days,0),0)->strong_day_ratio, + (COALESCE(avg_completion_percent,0)*0.45 + + COALESCE(longest_strong_day_streak,0)*2.00 + + COALESCE(completed_task_events,0)*0.35)->discipline_composite_score}(R0) +R <- omega_{ORDER BY discipline_composite_score DESC, user_id ASC; + DENSE_RANK()->discipline_annual_rank}(R1) Index: db-scripts/p6-relational-algebra/p6_q4_annual_investing_diversification_concentration_ra.txt =================================================================== --- db-scripts/p6-relational-algebra/p6_q4_annual_investing_diversification_concentration_ra.txt (revision 89156c1feb4b06a2d4ecb2bbac89f7c39f0df7e2) +++ db-scripts/p6-relational-algebra/p6_q4_annual_investing_diversification_concentration_ra.txt (revision 89156c1feb4b06a2d4ecb2bbac89f7c39f0df7e2) @@ -0,0 +1,64 @@ + +IB <- pi_{iu.user_id, u.username, u.email} + (investor_users iu bowtie_{iu.user_id = u.user_id} users u) + +AAL <- pi_{a.user_id, a.ticker_symbol, + COALESCE(a.quantity,0)->quantity, + COALESCE(a.buy_price,0)->buy_price, + COALESCE(a.quantity,0)*COALESCE(a.buy_price,0)->invested_amount, + a.buy_date} + (sigma_{YEAR(a.buy_date)=Y}(assets a)) + +TR <- gamma_{user_id, ticker_symbol; + SUM(quantity)->total_quantity, + SUM(invested_amount)->total_invested_amount, + COUNT(*)->lot_count, + MIN(buy_date)->first_buy_date, + MAX(buy_date)->last_buy_date}(AAL) + +PT <- gamma_{user_id; + SUM(total_invested_amount)->annual_total_invested, + SUM(lot_count)->annual_lot_count, + COUNT(*)->distinct_tickers}(TR) + +W0 <- TR bowtie_{TR.user_id = PT.user_id} PT +W1 <- alpha_{total_invested_amount/NULLIF(annual_total_invested,0)->position_weight}(W0) +W <- omega_{PARTITION BY user_id ORDER BY total_invested_amount DESC, ticker_symbol ASC; + DENSE_RANK()->position_rank}(W1) + +C <- gamma_{user_id; + SUM(position_weight*position_weight)->hhi_concentration, + MAX(position_weight)->top_position_weight, + MAX_IF(ticker_symbol, position_rank=1)->top_ticker}(W) + +IBM <- IB x M +AY <- sigma_{YEAR(a.buy_date)=Y}(assets a) +MI0 <- IBM leftouterjoin_{IBM.user_id=a.user_id AND IBM.month_no=MONTH(a.buy_date)} AY +MI <- gamma_{user_id, month_no; + SUM(COALESCE(a.quantity,0)*COALESCE(a.buy_price,0))->monthly_invested_amount}(MI0) +MS <- gamma_{user_id; + AVG(monthly_invested_amount)->avg_monthly_contribution, + STDDEV_SAMP(monthly_invested_amount)->contribution_stddev, + COUNT_IF(monthly_invested_amount>0)->active_investing_months}(MI) + +R0 <- IB + leftouterjoin_{IB.user_id=PT.user_id} PT + leftouterjoin_{IB.user_id=C.user_id} C + leftouterjoin_{IB.user_id=MS.user_id} MS +R1 <- alpha_{COALESCE(annual_total_invested,0)->annual_total_invested_nz, + COALESCE(annual_lot_count,0)->annual_lot_count_nz, + COALESCE(distinct_tickers,0)->distinct_tickers_nz, + COALESCE(avg_monthly_contribution,0)->avg_monthly_contribution_nz, + COALESCE(active_investing_months,0)->active_investing_months_nz, + COALESCE(active_investing_months,0)/12.0->activity_ratio, + COALESCE(hhi_concentration,0)->hhi_concentration_nz, + 1-COALESCE(hhi_concentration,1)->diversification_index, + COALESCE(top_position_weight,0)->top_position_weight_nz, + COALESCE(contribution_stddev/NULLIF(avg_monthly_contribution,0),0)->contribution_volatility_cv}(R0) +R <- omega_{ORDER BY diversification_index DESC, + annual_total_invested_nz DESC, + active_investing_months_nz DESC, + user_id ASC; + DENSE_RANK()->investing_annual_rank}(R1) + +