import { useMemo, useRef, useState } from "react"; import { useDiveState, useSQLQuery } from "@motherduck/react-sql-query"; import * as d3 from "d3"; // MotherDuck Dive: "Cloud is regional" — which cloud do data job ads ask for, by country / US state. // Everything on the page is computed live from cloud_regionality_public.main.jev_region_stats / jev_region_totals, // which are the aggregates of the prompt_jev() classification (see sql/40_jev_aggregates.sql). // The public share holds aggregates and map shapes only: no ad text, company names or personal data. export const REQUIRED_DATABASES = [ // Public (unrestricted) share of cloud_regionality_public, aggregates only. { type: "share", path: "md:_share/cloud_regionality_public/10d8fd77-a5f7-42e5-95d5-6cd999bf8535", alias: "cloud_regionality_public" }, ]; const N = (v: unknown): number => (v == null ? 0 : Number(v)); const fmt = (v: number) => v.toLocaleString("en-US"); const pct = (v: number) => `${v.toFixed(0)}%`; const TEXT = "#231f20"; const MUTED = "#6a6a6a"; const BG = "#f8f8f8"; const NO_DATA = "#e2e2e2"; const CLOUDS = ["azure", "aws", "gcp", "other"] as const; type Cloud = (typeof CLOUDS)[number]; const CLOUD_COLOR: Record = { azure: "#2a78d6", aws: "#eb6834", gcp: "#1baf7a", other: "#6a6a6a" }; const CLOUD_LABEL: Record = { azure: "Azure", aws: "AWS", gcp: "Google Cloud", other: "Other" }; const METRO_LABEL: Record = { london: "London", paris: "Paris", amsterdam: "Amsterdam", berlin: "Berlin", madrid: "Madrid", copenhagen: "Copenhagen", new_york: "New York", san_francisco: "San Francisco", }; const METRO_COUNTRY: Record = { london: "GB", paris: "FR", amsterdam: "NL", berlin: "DE", madrid: "ES", copenhagen: "DK", new_york: "US", san_francisco: "US", }; // --- Types & aggregation ----------------------------------------------------------------------- type Region = { level: string; code: string; nAds: number; nDec: number; azure: number; aws: number; gcp: number; other: number; leader: Cloud; leaderPct: number; }; function aggregate(rows: readonly Record[]): Map { const out = new Map(); for (const r of rows) { const level = String(r.level); const code = String(r.region); const key = `${level}:${code}`; let reg = out.get(key); if (!reg) { reg = { level, code, nAds: N(r.n_ads), nDec: N(r.n_dec), azure: 0, aws: 0, gcp: 0, other: 0, leader: "other", leaderPct: 0 }; out.set(key, reg); } const c = String(r.cloud); if (c === "azure" || c === "aws" || c === "gcp") reg[c] += N(r.n); else reg.other += N(r.n); } for (const reg of out.values()) { let best: Cloud = "other"; for (const c of CLOUDS) if (reg[c] > reg[best]) best = c; reg.leader = best; reg.leaderPct = reg.nDec > 0 ? (100 * reg[best]) / reg.nDec : 0; } return out; } const share = (r: Region, c: Cloud) => (r.nDec > 0 ? (100 * r[c]) / r.nDec : 0); // --- Map --------------------------------------------------------------------------------------- type Feature = { code: string; name: string; geo: d3.GeoPermissibleObjects }; // d3-geo needs exterior rings wound clockwise on the sphere (holes the other way). GeoJSON sources // disagree on this, and a wrong ring covers the complement of the earth, so normalise every ring. function rewind(geometry: any): any { const fixRing = (ring: number[][], exterior: boolean) => { const area = d3.geoArea({ type: "Polygon", coordinates: [ring] }); const small = area <= 2 * Math.PI; return small === exterior ? ring : ring.slice().reverse(); }; const fixPolygon = (rings: number[][][]) => rings.map((ring, i) => fixRing(ring, i === 0)); if (geometry.type === "Polygon") return { ...geometry, coordinates: fixPolygon(geometry.coordinates) }; if (geometry.type === "MultiPolygon") return { ...geometry, coordinates: geometry.coordinates.map(fixPolygon) }; return geometry; } type Focus = Cloud | "all"; // What a circle encodes: "all" = leading cloud (size = all counted ads); a cloud = that cloud only // (size = its ads in the region, colour intensity = its share there). const valueOf = (r: Region, focus: Focus) => (focus === "all" ? r.nDec : r[focus]); const shareOf = (r: Region, focus: Focus) => (focus === "all" ? r.leaderPct : share(r, focus)); const colourOf = (r: Region, focus: Focus) => CLOUD_COLOR[focus === "all" ? r.leader : focus]; const intensity = (s: number) => Math.max(0, Math.min(1, (s - 5) / 60)); // 5% share -> pale, 65%+ -> full // Reference circles for the size scale, kept outside the map so they never read as data. function SizeLegend({ maxVal, maxRadius, focus }: { maxVal: number; maxRadius: number; focus: Focus }) { const rScale = d3.scaleSqrt().domain([0, maxVal]).range([0, maxRadius]); const steps = [100, 500, 1000, 5000].filter((v) => v <= maxVal * 1.3).slice(-2); const big = rScale(steps[steps.length - 1] ?? 1); const h = 2 * big + 4; let x = 0; const items = steps.map((v) => { const r = rScale(v); const cx = x + r; x += 2 * r + 34; return { v, r, cx }; }); return (

{focus === "all" ? "ads counted" : `${CLOUD_LABEL[focus]} ads`}

); } function RegionMap({ features, regions, level, focus, width, height, maxRadius, maxVal }: { features: Feature[]; regions: Map; level: "country" | "us_state"; focus: Focus; width: number; height: number; maxRadius: number; maxVal: number; }) { const box = useRef(null); const [hover, setHover] = useState<{ x: number; y: number; f: Feature; r?: Region } | null>(null); const path = useMemo(() => { const extent: [[number, number], [number, number]] = [[6, 6], [width - 6, height - 6]]; if (level === "us_state") { const fc = { type: "FeatureCollection", features: features.map((f) => f.geo) } as d3.GeoPermissibleObjects; return d3.geoPath(d3.geoAlbersUsa().fitExtent(extent, fc)); } // Fixed frame for Europe so overseas territories do not stretch the projection. // d3 reads rings on the sphere: a counter-clockwise ring means "everything except this box". const frame = rewind({ type: "Polygon", coordinates: [[[-11, 34.5], [32, 34.5], [32, 71.5], [-11, 71.5], [-11, 34.5]]] }); return d3.geoPath(d3.geoConicConformal().rotate([-10, 0]).parallels([40, 65]).fitExtent(extent, frame)); }, [features, level, width, height]); // Region fill is a light tint: the leading cloud (all) or the focused cloud shaded by its share. const fillFor = (r?: Region) => { if (!r) return NO_DATA; const t = focus === "all" ? 0.28 : 0.08 + 0.42 * intensity(shareOf(r, focus)); return d3.interpolateRgb("#ffffff", colourOf(r, focus))(t); }; // One circle per region, area proportional to the ads counted (for the focused cloud), anchored at // the centroid and nudged apart with a short force simulation so small neighbours stay readable. const rScale = d3.scaleSqrt().domain([0, maxVal]).range([0, maxRadius]); const bubbles = useMemo(() => { const nodes = features.flatMap((f) => { const r = regions.get(`${level}:${f.code}`); if (!r || valueOf(r, focus) <= 0) return []; const [cx, cy] = path.centroid(f.geo); if (!Number.isFinite(cx) || !Number.isFinite(cy)) return []; return [{ f, r, x0: cx, y0: cy, x: cx, y: cy, radius: rScale(valueOf(r, focus)) }]; }); const sim = d3.forceSimulation(nodes as any) .force("x", d3.forceX((d: any) => d.x0).strength(0.6)) .force("y", d3.forceY((d: any) => d.y0).strength(0.6)) .force("collide", d3.forceCollide((d: any) => d.radius + 1.5).iterations(3)) .stop(); for (let i = 0; i < 120; i++) sim.tick(); return nodes; }, [features, regions, level, focus, path, maxVal, maxRadius]); const circleOpacity = (r: Region) => (focus === "all" ? 0.92 : 0.3 + 0.65 * intensity(shareOf(r, focus))); const onHover = (e: React.MouseEvent, f: Feature, r?: Region) => { const b = box.current?.getBoundingClientRect(); setHover({ x: e.clientX - (b?.left ?? 0), y: e.clientY - (b?.top ?? 0), f, r }); }; return (

{hover && (

{hover.f.name}

{hover.r ? ( <>

{fmt(hover.r.nDec)} ads counted · {fmt(hover.r.nAds)} analysed

{CLOUDS.filter((c) => hover.r![c] > 0).map((c) => (

{CLOUD_LABEL[c]} {share(hover.r!, c).toFixed(1)}% · {fmt(hover.r![c])}

))} ) : (

no job-ad data

)}

); } // --- Small building blocks ---------------------------------------------------------------------- function Kpi({ value, label, loading, color }: { value: string; label: string; loading: boolean; color?: string }) { return (

{loading ?

: (

{value}

)}

{label}

); } function ShareCell({ r, c }: { r: Region; c: Cloud }) { const v = share(r, c); const lead = r.leader === c; return ( {v > 0 ? `${v.toFixed(0)}%` : "–"} ); } function RegionTable({ rows, names, title, note }: { rows: Region[]; names: Map; title: string; note?: string }) { return (

{title}

{note &&

{note}

} {rows.map((r) => ( ))}

Region	Ads analysed	Ads counted	Azure	AWS	GCP	Other
{names.get(r.code) ?? r.code}	{fmt(r.nAds)}	{fmt(r.nDec)}

); } // --- Page -------------------------------------------------------------------------------------- export default function CloudRegionalityDive() { const [view, setView] = useDiveState<"europe" | "us">("view", "europe"); const [focus, setFocus] = useDiveState("cloud", "all"); const level: "country" | "us_state" = view === "europe" ? "country" : "us_state"; // Strict definition, baked in: demand-side ads (not posted by a cloud vendor), one cloud named in the // ad, Jev confidence >= 0.8, and a decided answer (not "multi-cloud" / "mentioned in passing"). const stats = useSQLQuery(` SELECT s.level, s.region, s.cloud, s.n_strict AS n, t.n_ads, t.n_decided_strict AS n_dec FROM "cloud_regionality_public"."main"."jev_region_stats" s JOIN "cloud_regionality_public"."main"."jev_region_totals" t ON t.level = s.level AND t.region = s.region AND t.period = s.period WHERE s.period IN ('2024_25', '2025') AND s.cloud NOT IN ('multi', 'passing') AND s.n_strict > 0 `); const funnel = useSQLQuery(` SELECT (SELECT n_classified FROM "cloud_regionality_public"."main"."classification_summary") AS n_classified, sum(n_ads + n_own) AS n_window, sum(n_own) AS n_own, sum(n_ads) AS n_demand, sum(n_multi_mentioned) AS n_multi, sum(n_decided_strict) AS n_dec FROM "cloud_regionality_public"."main"."jev_region_totals" WHERE period IN ('2024_25', '2025') `); const euShapes = useSQLQuery(` SELECT country_code AS code, country_name AS name, ST_AsGeoJSON(ST_Simplify(geom, 0.03)) AS gj FROM "cloud_regionality_public"."main"."country_shapes" `); const usShapes = useSQLQuery(` SELECT st AS code, name, geojson AS gj FROM "cloud_regionality_public"."main"."us_state_shapes" WHERE st <> 'PR' `); const statRows = Array.isArray(stats.data) ? stats.data : []; const regions = useMemo(() => aggregate(statRows), [statRows]); const parseShapes = (rows: unknown): Feature[] => (Array.isArray(rows) ? rows : []).map((r) => ({ code: String(r.code), name: String(r.name), geo: { type: "Feature", properties: {}, geometry: rewind(JSON.parse(String(r.gj))) } as d3.GeoPermissibleObjects, })); const euFeatures = useMemo(() => parseShapes(euShapes.data), [euShapes.data]); const usFeatures = useMemo(() => parseShapes(usShapes.data), [usShapes.data]); const features = view === "europe" ? euFeatures : usFeatures; const shapesQuery = view === "europe" ? euShapes : usShapes; const names = useMemo(() => { const m = new Map(); for (const f of euFeatures) m.set(f.code, f.name); for (const f of usFeatures) m.set(f.code, f.name); return m; }, [euFeatures, usFeatures]); const viewRegions = useMemo( () => [...regions.values()].filter((r) => r.level === level).sort((a, b) => valueOf(b, focus) - valueOf(a, focus)), [regions, level, focus], ); const metros = useMemo( () => [...regions.values()].filter((r) => r.level === "metro").sort((a, b) => b.nDec - a.nDec), [regions], ); // View-level KPIs: totals, overall leader, and how many regions each cloud leads. const kpi = useMemo(() => { const tot = { nAds: 0, nDec: 0, azure: 0, aws: 0, gcp: 0, other: 0 }; const led: Record = { azure: 0, aws: 0, gcp: 0, other: 0 }; const ledAds: Record = { azure: 0, aws: 0, gcp: 0, other: 0 }; // ads counted in regions each cloud leads for (const r of viewRegions) { tot.nAds += r.nAds; tot.nDec += r.nDec; for (const c of CLOUDS) tot[c] += r[c]; led[r.leader] += 1; ledAds[r.leader] += r.nDec; } let top: Cloud = "azure"; for (const c of CLOUDS) if (tot[c] > tot[top]) top = c; let mostLed: Cloud = "azure"; for (const c of CLOUDS) if (led[c] > led[mostLed]) mostLed = c; // With a cloud selected, KPIs 3 and 4 describe that cloud instead of the overall winner. if (focus !== "all") { top = focus; mostLed = focus; } return { ...tot, top, topPct: tot.nDec ? (100 * tot[top]) / tot.nDec : 0, mostLed, ledCount: led[mostLed], nRegions: viewRegions.length, ledAdsPct: tot.nDec ? (100 * ledAds[mostLed]) / tot.nDec : 0, }; }, [viewRegions, focus]); const f = Array.isArray(funnel.data) && funnel.data[0] ? funnel.data[0] : null; const regionWord = view === "europe" ? "countries" : "states"; const loading = stats.isLoading; const maxRadius = view === "europe" ? 36 : 30; const maxVal = Math.max(1, ...viewRegions.map((r) => valueOf(r, focus))); const tab = (key: "europe" | "us", label: string) => ( ); return (

Cloud is regional: which cloud do data job ads ask for?

Job ads from 2024–25, classified with MotherDuck's prompt_jev(). Each region gets a circle sized by how many ads it contributes and coloured by the cloud most of them are built on. Only ads that name a single cloud with Jev confidence ≥ 0.8 are counted.

{tab("europe", "Europe")} {tab("us", "United States")} {view === "europe" ? "Google Jobs, 21 countries" : "Google Jobs, 50 states + DC"}

Show {(["all", "azure", "aws", "gcp"] as Focus[]).map((c) => { const on = focus === c; const colour = c === "all" ? TEXT : CLOUD_COLOR[c]; return ( ); })}

{shapesQuery.isLoading || stats.isLoading ? (

) : shapesQuery.isError || stats.isError ? (

{String(shapesQuery.error ?? stats.error)}

) : ( )}

{focus === "all" ? "Circle area = ads counted in the region; circle colour and the region tint = leading cloud; the number is its share." : `Circle area = ${CLOUD_LABEL[focus]} ads counted in the region; darker circle and tint = larger ${CLOUD_LABEL[focus]} share; the number is that share.`} {" "}Hover for counts and the full split. Grey = no ads in the dataset.

{!loading && }

{stats.isLoading ? (

) : ( )}

{stats.isLoading ? (

) : ( [m.code, `${METRO_LABEL[m.code] ?? m.code} (${METRO_COUNTRY[m.code] ?? ""})`]))} /> )}

How it is computed

{funnel.isLoading || !f ? (

) : (

{fmt(N(f.n_classified))} data job ads mention a cloud provider (Google Jobs Europe 2022–25 and US 2024–25, LinkedIn 2025 for eight cities). Each ad was sent to prompt_jev() with the question "Which cloud platform is this data job's stack primarily built on?" and 14 possible answers (AWS, Azure, Google Cloud, 9 other providers, multi-cloud, mentioned in passing).
{fmt(N(f.n_window))} of them are 2024–25 postings with a known country, state or city. {fmt(N(f.n_own))} posted by the cloud vendors themselves are dropped, leaving {fmt(N(f.n_demand))} demand-side ads ("ads analysed").
{fmt(N(f.n_multi))} ads name two or more clouds, so their primary cloud is ambiguous. These, plus answers with Jev confidence below 0.8 or an undecided answer, are left out. {fmt(N(f.n_dec))} ads remain ("ads counted").
Share = counted ads for a cloud ÷ counted ads in the region. The circle colour is the cloud with the largest share and the circle area is the region's counted ads, so a small country cannot look as important as a large one. The "regions led" KPI is also given as the share of all counted ads those regions hold.

)}

Caveats: Google Jobs coverage varies by country (Britain and France have the most ads, Luxembourg the fewest), and ads are a proxy for demand, not spend. 2022–23 European ads exist in the same table and tell the same story.

); }

Cloud is regional: which cloud do data job ads ask for?

Comments