Analyzing 2.7M U.S. flood-insurance claims with SQL
Some of my findings using FEMA's public NFIP claims data. I started by loading 2.7 million records into SQL Server using bulk insert. Then using window functions and conditional aggregation I tried to find where property-flood losses concentrate, how they trend over decades, and where the data itself will mislead you if you aren't careful.
of all flood-insurance dollars paid, since 1978, have been in 2022 and 2024. This may seem obvious given that both those years had really bad hurricane storm seasons. Given that these years of large claims align with horrible hurricane storms I don't think you can draw future conclusions from this, but one could make an argument that the risk isn't historical, it's escalating.
The question
How have flood-insurance losses in the U.S. evolved over the last five decades, where are they concentrated, and what does that pattern mean for a property insurer? I used FEMA's redacted NFIP claims dataset which contains 2,721,780 claim transactions across 57 states and territories, loss dates 1978–2026 to answer these questions.
What I found
Losses concentrate in a handful of coastal states. Louisiana, Florida, and Texas account for the large majority of all dollars ever paid. Again this may seem obvious because it would be driven by named hurricane catastrophes. Louisiana and Florida are neck-and-neck on building payouts, but Louisiana's contents losses run far higher. This came as a surprise to me given that Louisiana has a much smaller population than Florida.
Year-over-year (YoY) change lines up directly with hurricane storm timing, not a trend.
Computing YoY change with a LAG window function produces some wild variations.
+695% in 2005, mostly due to hurricane Katrina, followed by −96% the next
year, and the same shape in 2012 and 2017. On catastrophe exposed lines,
YoY is just a question of "did a major storm make landfall?" and
shouldn't be extrapolated.
Florida's cumulative losses are found in the most recent years. A running total shows losses accumulating slowly for decades, then more than doubling in 2022, which would be to due to hurrican Ian, and again in 2024. More than half of all flood dollars ever paid in Florida landed in just those two years.
What the data doesn't tell you at face value
The findings are only half the battle and you need to find where the data has gaps. Three traps this analysis surfaced and handled:
-
A coded field that changed meaning over time.
The occupancy type field mixes a legacy code set and an expanded post 2021 set.
I first did a
GROUP BYthe year to compare different classification schemes and see if my thinking was correct. Then, after some research, I found FEMA updated their pricing approach with Risk Rating 2.0 and this expanded the occupancy type codes. - Averages distorted by zero payout claims. Averaging over all claims includes closed without payment records. So "average paid" and "average paid when a claim pays" are different questions.
- A moving average that counts rows, not years. A row based window assumes there are no gaps in the years the data reports on. On sparse or smaller territories a "3 year" window can silently span decades. The fix, and what I plan to do next, is to build a complete year spine first.
Noticing these small gaps is the analyst job, not just writing SQL. A computer, or programming launguage, will only do what you ask of it.
Tools
Microsoft SQL Server (T-SQL) · SSMS · Power BI