import { useMemo } from "react"; import { useSQLQuery, useDiveState } from "@motherduck/react-sql-query"; import { ComposedChart, LineChart, Line, Area, XAxis, YAxis, CartesianGrid, Tooltip, ResponsiveContainer, Legend, } from "recharts"; const N = (v: unknown): number => (v != null ? Number(v) : 0); // Medium blue → dark blue → black: older years are muted but still legible // on white, current year is near-black. 2021 starts at a saturated mid-blue // (not a washed-out tint) so it reads clearly against the card background. const YEAR_COLORS: Record = { 2021: "#6ba3c4", 2022: "#4a8bb5", 2023: "#2d72a3", 2024: "#155b8a", 2025: "#0a3f63", 2026: "#231f20", }; const YEAR_WIDTHS: Record = { 2021: 1.2, 2022: 1.4, 2023: 1.7, 2024: 2.0, 2025: 2.3, 2026: 2.8, }; const MONTH_LABELS = ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]; const IQR_COLOR = "rgba(7, 119, 179, 0.13)"; const MEDIAN_COLOR = "#0777b3"; // Pin Inter for consistent typography across viewers (system-ui fallback is safe // if the web font fails to load in the runtime). const FONT_STACK = "Inter, system-ui, -apple-system, sans-serif"; const PAGE_BG = "#e3e7ef"; // Soft two-layer shadow so white cards lift off the tinted page background const CARD_SHADOW = "0 1px 2px rgba(28,32,48,0.04), 0 6px 22px rgba(28,32,48,0.07)"; // Shared card surface const CARD = { background: "white", borderRadius: 10, border: "1px solid #e6e8ee", boxShadow: CARD_SHADOW, marginBottom: 16, padding: "20px 24px", }; // Section label: small uppercase eyebrow above a bold title function SecLabel({ text }: { text: string }) { return (

{text}

); } function fmtPrice(v: number): string { return v >= 1000 ? `$${(v / 1000).toFixed(0)}K` : `$${v}`; } function fmtPayment(v: number): string { return `$${Math.round(v).toLocaleString()}`; } function fmtPct(v: number): string { return `${v > 0 ? "+" : ""}${v.toFixed(1)}%`; } function toTitleCase(s: string): string { return s.toLowerCase().replace(/\b\w/g, c => c.toUpperCase()); } function fmtMillions(v: number): string { return v >= 1_000_000 ? `$${(v / 1_000_000).toFixed(1)}M` : v <= -1_000_000 ? `-$${(Math.abs(v) / 1_000_000).toFixed(1)}M` : fmtPrice(v); } export const REQUIRED_DATABASES = [ { type: "share", path: "md:_share/wi_home_affordability_share/cdc58229-9a9c-41ce-a50b-4affa3095a0c", alias: "wi_home_affordability", }, ]; export default function WIAffordabilityDive() { const [county, setCounty] = useDiveState("county", "WI"); const [view, setView] = useDiveState<"yoy" | "timeline">("view", "yoy"); const [selectedEntity, setSelectedEntity] = useDiveState("entity", ""); const safeCounty = county.replace(/'/g, "''"); const isState = county === "WI"; const geoLevel = isState ? "state" : "county"; const safeEntity = selectedEntity.replace(/'/g, "''"); const geoQuery = useSQLQuery(`SELECT geojson FROM "wi_home_affordability"."main"."county_geojson" LIMIT 1`, { enabled: !!selectedEntity }); const countyGeo = useMemo(() => { const rows = Array.isArray(geoQuery.data) ? geoQuery.data : []; if (!rows.length) return null; try { return JSON.parse(rows[0].geojson as string); } catch { return null; } }, [geoQuery.data]); const countiesQuery = useSQLQuery(` SELECT DISTINCT county_name FROM "wi_home_affordability"."main"."dive_sf_county_monthly" WHERE geography_level = 'county' AND sales_count >= 10 ORDER BY county_name `); const counties = (Array.isArray(countiesQuery.data) ? countiesQuery.data : []) .map(r => r.county_name as string); const mainQuery = useSQLQuery(` SELECT strftime(month, '%Y-%m') AS month_str, EXTRACT(YEAR FROM month)::INT AS yr, EXTRACT(MONTH FROM month)::INT AS mo, sales_count, median_sale_price, p25_sale_price, p75_sale_price, rate_pct, est_payment, p25_payment, p75_payment FROM "wi_home_affordability"."main"."dive_sf_county_monthly" WHERE geography_level = '${geoLevel}' AND county_name = '${safeCounty}' ORDER BY month `); const rows = Array.isArray(mainQuery.data) ? mainQuery.data : []; const parsed = useMemo(() => rows.map(r => ({ month_str: r.month_str as string, yr: N(r.yr), mo: N(r.mo), sales_count: N(r.sales_count), price: N(r.median_sale_price), p25: r.p25_sale_price != null ? N(r.p25_sale_price) : null as number | null, p75: r.p75_sale_price != null ? N(r.p75_sale_price) : null as number | null, rate_pct: r.rate_pct != null ? N(r.rate_pct) : null as number | null, payment: r.est_payment != null ? N(r.est_payment) : null as number | null, p25_payment: r.p25_payment != null ? N(r.p25_payment) : null as number | null, p75_payment: r.p75_payment != null ? N(r.p75_payment) : null as number | null, })), [rows]); const latest = parsed.length > 0 ? parsed[parsed.length - 1] : null; const yearAgo = parsed.length >= 13 ? parsed[parsed.length - 13] : null; const priceYoY = latest && yearAgo && yearAgo.price > 0 ? (latest.price - yearAgo.price) / yearAgo.price * 100 : null; const paymentYoY = latest?.payment && yearAgo?.payment && yearAgo.payment > 0 ? (latest.payment - yearAgo.payment) / yearAgo.payment * 100 : null; const may2021 = parsed.find(r => r.yr === 2021 && r.mo === 5); const may2026 = parsed.find(r => r.yr === 2026 && r.mo === 5); const paymentLift = may2021?.payment && may2026?.payment ? (may2026.payment - may2021.payment) / may2021.payment * 100 : null; const priceLift = may2021 && may2026 ? (may2026.price - may2021.price) / may2021.price * 100 : null; const years = useMemo(() => { const seen = new Set(); parsed.forEach(r => seen.add(r.yr)); return Array.from(seen).sort(); }, [parsed]); const priceYoyData = useMemo(() => { const byKey = new Map(); parsed.forEach(r => byKey.set(`${r.yr}-${r.mo}`, r)); return MONTH_LABELS.map((label, i) => { const mo = i + 1; const point: Record = { month: label }; years.forEach(yr => { const row = byKey.get(`${yr}-${mo}`); point[String(yr)] = row ? row.price : null; }); return point; }); }, [parsed, years]); const priceTrendData = useMemo(() => parsed.map(r => ({ month: r.month_str, median: r.price, iqr_lo: r.p25 ?? 0, iqr_hi: r.p75 != null && r.p25 != null && r.p75 > r.p25 ? r.p75 - r.p25 : 0, p25_val: r.p25, p75_val: r.p75, })), [parsed]); const paymentYoyData = useMemo(() => { const byKey = new Map(); parsed.forEach(r => byKey.set(`${r.yr}-${r.mo}`, r)); return MONTH_LABELS.map((label, i) => { const mo = i + 1; const point: Record = { month: label }; years.forEach(yr => { const row = byKey.get(`${yr}-${mo}`); point[String(yr)] = row ? (row.payment ?? null) : null; }); return point; }); }, [parsed, years]); const paymentTrendData = useMemo(() => parsed.map(r => ({ month: r.month_str, median: r.payment ?? 0, iqr_lo: r.p25_payment ?? 0, iqr_hi: r.p75_payment != null && r.p25_payment != null && r.p75_payment > r.p25_payment ? r.p75_payment - r.p25_payment : 0, p25_val: r.p25_payment, p75_val: r.p75_payment, })), [parsed]); const rateTrendData = useMemo(() => parsed .filter(r => r.rate_pct != null) .map(r => ({ month: r.month_str, rate: r.rate_pct })), [parsed]); const buyersQuery = useSQLQuery(` SELECT entity, purchases, total_spent, avg_price, name_variants, sales_count, total_received, net FROM "wi_home_affordability"."main"."dive_corporate_buyers" WHERE county_name = '${safeCounty}' ORDER BY purchases DESC LIMIT 15 `); const buyerRows = Array.isArray(buyersQuery.data) ? buyersQuery.data : []; const parcelQuery = useSQLQuery(` SELECT latitude AS lat, longitude AS lon, site_address AS address, county_name FROM "wi_home_affordability"."main"."dive_parcel_footprint" WHERE entity = '${safeEntity}' LIMIT 500 `, { enabled: !!selectedEntity }); const parcelPoints = useMemo(() => { const data = Array.isArray(parcelQuery.data) ? parcelQuery.data : []; return data .map(r => ({ lat: N(r.lat), lon: N(r.lon), address: (r.address as string) || "", county: (r.county_name as string) || "", })) .filter(p => p.lat > 40 && p.lon < -80); }, [parcelQuery.data]); const isLoading = mainQuery.isLoading; const geoLabel = isState ? "Wisconsin" : `${toTitleCase(county)} County`; const renderChart = ( yoyData: Record[], trendData: { month: string; median: number; iqr_lo: number; iqr_hi: number; p25_val: number | null; p75_val: number | null }[], yAxisFmt: (v: number) => string, ttFmt: (v: number) => string, ) => { if (isLoading) return

; if (view !== "timeline") return ( min * 0.96, (max: number) => max * 1.04]} allowDataOverflow={false} /> { if (!payload?.length) return null; const items = [...payload].reverse().filter(p => p.value != null); if (!items.length) return null; return (

{label}

{items.map(p => (

{p.name} {ttFmt(Number(p.value))}

))}

); }} /> {v}} /> {years.map(yr => ( ))} ); return ( { if (!payload?.length) return null; const d: Record = {}; payload.forEach(p => { if (p.dataKey) d[p.dataKey as string] = Number(p.value); }); const iqrLo = d.iqr_lo ?? 0; const p75 = iqrLo + (d.iqr_hi ?? 0); const med = d.median ?? 0; if (!med) return null; return (

{label}

{p75 > 0 &&

P75 {ttFmt(p75)}

}

Med {ttFmt(med)}

{iqrLo > 0 &&

P25 {ttFmt(iqrLo)}

}

); }} /> ); }; const MAP_W = 560, MAP_H = 595; const WI_LON_W = -92.9, WI_LON_E = -86.8; const WI_LAT_S = 42.5, WI_LAT_N = 47.1; const mapX = (lon: number) => ((lon - WI_LON_W) / (WI_LON_E - WI_LON_W)) * MAP_W; const mapY = (lat: number) => (1 - (lat - WI_LAT_S) / (WI_LAT_N - WI_LAT_S)) * MAP_H; return (

Wisconsin Single-Family Home Affordability

RETR filings 2021–2026 · FRED 30-yr fixed rate · 10% down, 30-yr amortization

Geography

View

{(["yoy", "timeline"] as const).map(v => ( ))}

{[ { value: latest ? fmtPrice(latest.price) : "—", label: "Median Sale Price", delta: priceYoY, deltaPositiveIsGood: true, border: true }, { value: latest?.payment ? fmtPayment(latest.payment) : "—", label: "Est. Monthly Payment", delta: paymentYoY, deltaPositiveIsGood: false, border: true }, { value: latest?.rate_pct != null ? `${N(latest.rate_pct).toFixed(2)}%` : "—", label: "30-yr Fixed Rate", delta: null, deltaPositiveIsGood: true, border: true }, { value: latest ? N(latest.sales_count).toLocaleString() : "—", label: "Monthly Sales", delta: null, deltaPositiveIsGood: true, border: false }, ].map(({ value, label, delta, deltaPositiveIsGood, border }) => (

{isLoading ?

{value}

}

{label}

{!isLoading && delta != null && (

= 0 : delta < 0) ? "#2d7a00" : "#bc1200" }}>{fmtPct(delta)} YoY

)}

))}

{!isLoading && paymentLift != null && priceLift != null && (

May 2021 → May 2026 Median price +{priceLift.toFixed(0)}% {" "}({may2021 ? fmtPrice(may2021.price) : "—"} → {may2026 ? fmtPrice(may2026.price) : "—"}) Monthly payment +{paymentLift.toFixed(0)}% {" "}({may2021?.payment ? fmtPayment(may2021.payment) : "—"} → {may2026?.payment ? fmtPayment(may2026.payment) : "—"})

)}

Median Sale Price — {geoLabel} {" · "}{view === "timeline" ? "Timeline · P25–P75 range" : "Year over Year"}

{renderChart(priceYoyData, priceTrendData, v => fmtPrice(v), v => fmtPrice(v))}

Est. Monthly Payment — {geoLabel} {" · "}10% down, 30-yr fixed{view === "timeline" ? " · P25–P75 range" : " · Year over Year"}

{renderChart(paymentYoyData, paymentTrendData, v => `$${(v / 1000).toFixed(1)}K`, v => fmtPayment(v))}

30-yr Fixed Rate · FRED PMMS (weekly avg.)

{isLoading ? (

) : ( `${v}%`} fontSize={9} tick={{ fill: "#bd4e35" }} width={26} axisLine={false} tickLine={false} /> [`${N(v).toFixed(2)}%`, "Rate"]} contentStyle={{ fontSize: 11, background: "white", border: "1px solid #e8e5df", borderRadius: 6, boxShadow: "0 2px 8px rgba(35,31,32,0.08)" }} /> )}

Top Corporate Buyers — {geoLabel}

LLC/Corp · name-normalized · 2021–2026

Click a row to map current parcel holdings

{buyersQuery.isLoading ? (

) : buyerRows.length === 0 ? (

No corporate buyers with 3+ purchases found for this geography.

) : ( {["#", "Entity (normalized)", "Buys", "Spent", "Sells", "Received", "Net", "Variants"].map(h => ( ))} {buyerRows.map((r, i) => { const isSelected = selectedEntity === (r.entity as string); const net = N(r.net); const netColor = net > 0 ? "#2d7a00" : "#adadad"; const salesCount = N(r.sales_count); return ( setSelectedEntity(isSelected ? undefined : (r.entity as string))} style={{ borderBottom: "1px solid #f0ede8", cursor: "pointer", background: isSelected ? "#e8f4fb" : "transparent" }}> ); })}

{h}
{i + 1}	{isSelected ? "▶ " : ""}{r.entity as string}	{N(r.purchases).toLocaleString()}	{fmtMillions(N(r.total_spent))}	0 ? "#231f20" : "#c8c4be" }}>{salesCount > 0 ? salesCount.toLocaleString() : "—"}	{salesCount > 0 ? fmtMillions(N(r.total_received)) : "—"}	{salesCount > 0 ? (net >= 0 ? "+" : "") + fmtMillions(net) : "—"}	1 ? "#e18727" : "#c8c4be" }}>{N(r.name_variants) > 1 ? `${N(r.name_variants)} variants` : "—"}

)}

{selectedEntity && (

{selectedEntity} {!parcelQuery.isLoading && parcelPoints.length > 0 && ( {parcelPoints.length} parcels · statewide current ownership )} {!parcelQuery.isLoading && parcelPoints.length === 0 && ( no parcel data available )}

{parcelQuery.isLoading ? (

Loading parcels…

) : parcelPoints.length === 0 ? (

No current parcel holdings found — entity may have sold or transferred since data was collected.

) : ( <>

Each dot = one parcel currently registered to this entity in the WI statewide parcel layer. Hover for address.

)}

Source: Wisconsin RETR filings (single-family, $1K–$5M) · FRED MORTGAGE30US · Payment = 10% down, 30-yr P&I · Buyer names normalized (UPPER + strip entity suffix + fund-number suffix); variants in orange · Sells/Net = times entity appeared as grantor in same period · Parcel map: WI Statewide Parcel dataset · County boundaries: WI DNR

); }

Wisconsin Single-Family Home Affordability

Comments