In [1]:
-- check the total rows in data table
SELECT count(*)
from public_pensions
(1 row(s) affected)
Total execution time: 00:00:00.978
Out[1]:
count
6071

Public Pension Plans Project¶

Identify Key Metrics in pension financials¶

KPI List items to query from SQL Database:¶

  • (Panel) Fundamentals - 5yr
  • (Panel) Member Count
  • (Panel) Inflows / outflows - 3yr
  • (Panel) Asset Allocation Mix
  • (Panel) Investment Income - 10yr
In [4]:
-- (Panel) Fundamentals

SELECT ppd_id, "PlanName", "PlanClosed",
cast(fy as int), 
cast("ActAssets_GASB" * 1000 as bigint) as "ActAssets_GASB", 
cast("UAAL_GASB" * 1000 as bigint) as "UAAL_GASB",
cast("TotFund_BnchmrkRtrn" / 100 as real) as "TotFund_BnchmrkRtrn", 
"InvestmentReturn_1yr", 
cast("FairValueChange_tot" * 1000 as bigint) as "FairValueChange_tot"
FROM public_pensions
WHERE "PlanClosed" = 0 and fy <=2022 and fy >= 2018 and ppd_id != 186
ORDER BY ppd_id, fy
(1010 row(s) affected)
Displaying Top 10 rows.
Total execution time: 00:00:00.096
Out[4]:
ppd_id PlanName PlanClosed fy ActAssets_GASB UAAL_GASB TotFund_BnchmrkRtrn InvestmentReturn_1yr FairValueChange_tot
1 Alabama ERS 0.0 2018 12240597000 5589138000 0.1155 0.0929 775132000
1 Alabama ERS 0.0 2019 12645789000 5897753000 0.0293 0.0278 30092000
1 Alabama ERS 0.0 2020 13491176000 6295296000 0.0853 0.0583 425632000
1 Alabama ERS 0.0 2021 14291093000 6813849000 0.24 0.2218 2558276000
1 Alabama ERS 0.0 2022 14291093000 6813849000 -0.1332 -0.1302 -2274633000
2 Alabama Teachers 0.0 2018 25006420000 10621979000 0.1112 0.0942 1615440000
2 Alabama Teachers 0.0 2019 25821326000 11394144000 0.026 0.0263 33671000
2 Alabama Teachers 0.0 2020 26681234000 11071566000 0.082 0.0562 801465000
2 Alabama Teachers 0.0 2021 28154228000 11952577000 0.2395 0.2262 5155012000
2 Alabama Teachers 0.0 2022 28154228000 11952577000 NULL -0.1318 -4500179000
In [7]:
-- (Panel) Member Count

SELECT ppd_id, "PlanName", "PlanClosed",
cast(fy as int), 
cast(beneficiaries_tot as int), 
cast(("TotMembership" - beneficiaries_tot - actives_tot) as int) as deferred_tot, 
cast(actives_tot as int),
cast("TotMembership" as int)
FROM public_pensions
WHERE "PlanClosed" = 0 and fy = 2021 and ppd_id != 186
ORDER BY ppd_id, fy
(16 row(s) affected)
Displaying Top 10 rows.
Total execution time: 00:00:00.014
Out[7]:
ppd_id PlanName PlanClosed fy beneficiaries_tot deferred_tot actives_tot TotMembership
1 Alabama ERS 0.0 2021 55916 24729 84571 165216
1 Alabama ERS 0.0 2022 55916 24729 84571 165216
2 Alabama Teachers 0.0 2021 103071 26329 134034 263434
2 Alabama Teachers 0.0 2022 103071 26329 134034 263434
5 Arizona Public Safety 0.0 2021 16690 2617 18366 37673
5 Arizona Public Safety 0.0 2022 17435 2901 18185 38521
6 Arizona SRS 0.0 2021 162967 250400 207913 621280
6 Arizona SRS 0.0 2022 167370 255622 208393 631385
7 Arkansas PERS 0.0 2021 40762 14802 42669 98233
7 Arkansas PERS 0.0 2022 41390 15066 42771 99227
In [8]:
-- (Panel) Inflows / Outflows

SELECT ppd_id, "PlanName", "PlanClosed",
cast(fy as int), 
cast("contrib_EE_regular" * 1000 as bigint) as "contrib_EE_regular", 
cast("contrib_ER_tot" * 1000 as bigint) as "contrib_ER_tot", 
cast(contrib_tot * 1000 as bigint) as "contrib_tot",
cast("FairValueChange_tot" * 1000 as bigint) as "FairValueChange_tot",
cast("expense_RetBenefits" * 1000 as bigint) as "expense_RetBenefits",
cast("expense_DeathBenefits" * 1000 as bigint) as "expense_DeathBenefits", 
cast("expense_DisabilityBenefits" * 1000 as bigint) as "expense_DisabilityBenefits", 
cast("expense_LumpSumBenefits" * 1000 as bigint) as "expense_LumpSumBenefits", 
cast("expense_SurvivorBenefits" * 1000 as bigint) as "expense_SurvivorBenefits",
cast(expense_refunds * 1000 as bigint) as "expense_refunds", 
cast("expense_AdminExpenses" * 1000 as bigint) as "expense_AdminExpenses",
cast(expense_net * 1000 as bigint) as "expense_net"
FROM public_pensions
WHERE "PlanClosed" = 0 and fy <=2022 and fy >= 2020 and ppd_id != 186
ORDER BY ppd_id, fy
(606 row(s) affected)
Displaying Top 10 rows.
Total execution time: 00:00:00.079
Out[8]:
ppd_id PlanName PlanClosed fy contrib_EE_regular contrib_ER_tot contrib_tot FairValueChange_tot expense_RetBenefits expense_DeathBenefits expense_DisabilityBenefits expense_LumpSumBenefits expense_SurvivorBenefits expense_refunds expense_AdminExpenses expense_net
1 Alabama ERS 0.0 2020 266056000 519806000 1135500000 425632000 -1209437000 NULL NULL NULL NULL NULL -13128000 -1277065000
1 Alabama ERS 0.0 2021 281975000 516402000 803049000 2558276000 -1260159000 NULL NULL NULL NULL NULL -10977000 -1335059000
1 Alabama ERS 0.0 2022 303507000 559869000 872433000 -2274633000 -1323657000 NULL NULL NULL NULL NULL -11306000 -1407290000
2 Alabama Teachers 0.0 2020 510818000 862475000 1378080000 801465000 -2328892000 NULL NULL NULL NULL NULL -21957000 -2425495000
2 Alabama Teachers 0.0 2021 519627000 874401000 1400533000 5155012000 -2415128000 NULL NULL NULL NULL NULL -20402000 -2512831000
2 Alabama Teachers 0.0 2022 553615000 932332000 1554199000 -4500179000 -2555391000 NULL NULL NULL NULL NULL -18945000 -2658331000
5 Arizona Public Safety 0.0 2020 132369000 953193000 1096041000 59646000 -754809000 NULL NULL NULL NULL -14341000 -8407000 -923171000
5 Arizona Public Safety 0.0 2021 136876000 1928807000 2075185000 2227079000 -804544000 NULL NULL NULL NULL -12762000 -11004000 -957987000
5 Arizona Public Safety 0.0 2022 144042000 3041767000 3202514000 -709924000 -860512000 NULL NULL NULL NULL -14775000 -9295000 -1039246000
6 Arizona SRS 0.0 2020 1272080000 1254651000 2540101000 127220000 -3228368000 NULL NULL NULL -52949000 -242252000 -26845000 -3551867000
In [9]:
-- (Panel) Asset Allocation Mix

SELECT ppd_id, "PlanName", "PlanClosed",
cast(fy as int), 
"OtherTotal_Actl", "AltMiscTotal_Actl", "CashTotal_Actl",
"COMDTotal_Actl", "HFTotal_Actl", "PETotal_Actl", "RETotal_Actl", "FITotal_Actl", "EQTotal_Actl"
FROM public_pensions
WHERE "PlanClosed" = 0 and fy = 2021 and ppd_id != 186
ORDER BY ppd_id, fy
(404 row(s) affected)
Displaying Top 10 rows.
Total execution time: 00:00:00.047
Out[9]:
ppd_id PlanName PlanClosed fy OtherTotal_Actl AltMiscTotal_Actl CashTotal_Actl COMDTotal_Actl HFTotal_Actl PETotal_Actl RETotal_Actl FITotal_Actl EQTotal_Actl
1 Alabama ERS 0.0 2021 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.2727 0.7273
1 Alabama ERS 0.0 2022 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.30319 0.69681
2 Alabama Teachers 0.0 2021 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.26898 0.73102
2 Alabama Teachers 0.0 2022 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.2964 0.7036
5 Arizona Public Safety 0.0 2021 0.0 0.0056 0.1045 0.0332 0.2059 0.2555 0.0 0.0397 0.3556
5 Arizona Public Safety 0.0 2022 0.0 0.0057 0.1572 0.0299 0.204 0.2372 0.0 0.0323 0.3337
6 Arizona SRS 0.0 2021 0.0 0.0 0.02 0.0 0.0 0.262 0.158 0.134 0.426
6 Arizona SRS 0.0 2022 0.0 0.0 0.016 0.0 0.0 0.127 0.198 0.304 0.355
7 Arkansas PERS 0.0 2021 0.0 0.0 0.0 0.0 0.04523 0.0 0.11256 0.14874 0.69347
7 Arkansas PERS 0.0 2022 NULL NULL NULL NULL NULL NULL NULL NULL NULL
In [10]:
-- (Panel) Investment Income

SELECT ppd_id, "PlanName", "PlanClosed",
cast(fy as int), 
cast("FairValueChange_tot" * 1000 as bigint) as "FairValueChange_tot"
FROM public_pensions
WHERE "PlanClosed" = 0 and fy <=2022 and fy >= 2013 and ppd_id != 186
ORDER BY ppd_id, fy
(2020 row(s) affected)
Displaying Top 10 rows.
Total execution time: 00:00:00.089
Out[10]:
ppd_id PlanName PlanClosed fy FairValueChange_tot
1 Alabama ERS 0.0 2013 984714000
1 Alabama ERS 0.0 2014 888303000
1 Alabama ERS 0.0 2015 -186154000
1 Alabama ERS 0.0 2016 753836000
1 Alabama ERS 0.0 2017 1098859000
1 Alabama ERS 0.0 2018 775132000
1 Alabama ERS 0.0 2019 30092000
1 Alabama ERS 0.0 2020 425632000
1 Alabama ERS 0.0 2021 2558276000
1 Alabama ERS 0.0 2022 -2274633000