-- check the total rows in data table
SELECT count(*)
from public_pensions
count |
---|
6071 |
-- (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
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 |
-- (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
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 |
-- (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
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 |
-- (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
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 |
-- (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
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 |