می‌خواهید زود بازنشسته شوید؟ پول بیشتری پس‌انداز کنید

WITH savings_rate_income_by_country
     AS (SELECT s.country
                AS
                country
                   ,
                s.savings_rate
                   AS savings_rate,
                i.average_monthly_net_salary_after_tax_
                AS
                   net_monthly_salary,
                i.average_monthly_net_salary_after_tax_ * 12
                AS
                   annual_income,
                i.average_monthly_net_salary_after_tax_ * 12 * ( 1 -
                s.savings_rate )
                AS
                annual_expense
         FROM   random_datasets.income_savings_rate_oecd_personal_savings_rate s
                INNER JOIN
                random_datasets.income_savings_rate_numbeo_monthly_salary
                i
                        ON s.country = i.country_name),
     years_elapsed
     AS (SELECT country,
                savings_rate,
                annual_income,
                annual_expense,
                Generate_series(1, 100)        AS year,
                annual_income - annual_expense AS annual_contribution
         FROM   savings_rate_income_by_country),
     compound_interest
     AS (SELECT a.*,
                b.annual_contribution * Power(( 1 + 0.05 ), a.year - b.year + 1)
                -
                b.annual_contribution AS compound_interest
         FROM   years_elapsed a
                LEFT JOIN years_elapsed b
                       ON a.year >= b.year
                          AND a.country = b.country
         ORDER  BY a.year),
     yoy_networth_table
     AS (SELECT country,
                savings_rate,
                annual_income,
                annual_expense,
                year,
                annual_contribution,
                Sum(annual_contribution) AS total_contribution,
                Sum(compound_interest)   AS total_interest,
                Sum(annual_contribution)
                + Sum(compound_interest) AS networth
         FROM   compound_interest
         GROUP  BY country,
                   savings_rate,
                   annual_income,
                   annual_expense,
                   year,
                   annual_contribution
         ORDER  BY year)
SELECT c.region                      AS "Region",
       y.country                     AS "Country",
       p.overall_pension_index_value AS "Pension Index Value",
       y.savings_rate                AS "Savings Rate",
       y.annual_income               AS "Annual Income",
       y.annual_expense              AS "Annual Expenses",
       Min(y.year)                   AS "Years to Retire",
       Min(y.networth)               AS "Projected Net Worth"
FROM   yoy_networth_table y
       LEFT JOIN random_datasets.countries_with_regional_codes c
              ON c.NAME = y.country
       LEFT JOIN random_datasets.income_savings_rate_global_pension_index_2021 p
              ON p.country = y.country
WHERE  y.networth * 0.04 >= y.annual_expense
GROUP  BY c.region,
          y.country,
          p.overall_pension_index_value,
          y.savings_rate,
          y.annual_income,
          y.annual_expense