import { useState } from "react";
import {
  LineChart, Line, BarChart, Bar,
  XAxis, YAxis, CartesianGrid, Tooltip,
  ResponsiveContainer, Cell
} from "recharts";
import { useSQLQuery } from "@motherduck/react-sql-query";


export const REQUIRED_DATABASES = [
  { type: 'share', path: 'md:_share/sample_data/ca7ad3fa-8709-4f9f-b7ec-b227b09d4ef2', alias: 'sample_data' }
];
const N = (v) => (v != null ? Number(v) : 0);
const PALETTE = ["#0777b3", "#bd4e35", "#2d7a00", "#e18727", "#638CAD", "#adadad"];
const LANGS = ["JavaScript", "Python", "SQL", "TypeScript", "Java", "C#"];

export default function StackOverflowDashboard() {
  const [activeLangs, setActiveLangs] = useState(new Set(LANGS));

  const toggleLang = (lang) => {
    setActiveLangs(prev => {
      const next = new Set(prev);
      if (next.has(lang)) { next.delete(lang); } else { next.add(lang); }
      return next;
    });
  };

  const salaryFmt = (v) => `$${(v / 1000).toFixed(0)}K`;

  // Respondents summary
  const respondentsQuery = useSQLQuery(`
    SELECT year, COUNT(*) as n
    FROM "sample_data"."stackoverflow_survey"."survey_results"
    GROUP BY year ORDER BY year
  `);
  const respondentsData = Array.isArray(respondentsQuery.data) ? respondentsQuery.data : [];
  const totalRespondents = respondentsData.reduce((s, r) => s + N(r.n), 0);
  const latestRow = respondentsData.find(r => r.year === "2024");
  const latestCount = latestRow ? N(latestRow.n) : 0;

  // Language trends
  const langQuery = useSQLQuery(`
    WITH base AS (
      SELECT year, trim(lang) as lang, COUNT(*) as cnt
      FROM "sample_data"."stackoverflow_survey"."survey_results",
        LATERAL unnest(string_split(LanguageHaveWorkedWith, ';')) t(lang)
      WHERE year IN ('2021','2022','2023','2024')
        AND LanguageHaveWorkedWith IS NOT NULL
        AND trim(lang) IN ('JavaScript','Python','SQL','TypeScript','Java','C#')
      GROUP BY year, lang
    ),
    totals AS (
      SELECT year, SUM(cnt) as total FROM base GROUP BY year
    )
    SELECT b.year, b.lang, ROUND(b.cnt * 100.0 / t.total, 1) as pct
    FROM base b JOIN totals t ON b.year = t.year
    ORDER BY b.year, b.lang
  `);
  const langRows = Array.isArray(langQuery.data) ? langQuery.data : [];
  const langByYear = {};
  for (const row of langRows) {
    if (!langByYear[row.year]) langByYear[row.year] = { year: row.year };
    langByYear[row.year][row.lang] = N(row.pct);
  }
  const langTrendData = ["2021","2022","2023","2024"].map(y => langByYear[y] || { year: y });

  // AI adoption
  const aiQuery = useSQLQuery(`
    SELECT
      CASE AISelect
        WHEN 'Yes' THEN 'Using AI tools'
        WHEN 'No, and I don''t plan to' THEN 'Not planning to'
        WHEN 'No, but I plan to soon' THEN 'Plan to soon'
        ELSE AISelect
      END as label,
      COUNT(*) as cnt
    FROM "sample_data"."stackoverflow_survey"."survey_results"
    WHERE year = '2024' AND AISelect IS NOT NULL AND AISelect != 'NA'
    GROUP BY AISelect ORDER BY cnt DESC
  `);
  const aiData = Array.isArray(aiQuery.data) ? aiQuery.data : [];
  const aiUsing = N(aiData.find(r => r.label === "Using AI tools")?.cnt);
  const aiTotal = aiData.reduce((s, r) => s + N(r.cnt), 0);
  const aiPct = aiTotal > 0 ? Math.round(aiUsing / aiTotal * 100) : 0;

  // Salary by role
  const salaryQuery = useSQLQuery(`
    SELECT
      CASE DevType
        WHEN 'Senior Executive (C-Suite, VP, etc.)' THEN 'C-Suite / VP'
        WHEN 'Engineering manager' THEN 'Eng Manager'
        WHEN 'Cloud infrastructure engineer' THEN 'Cloud Infra Eng'
        WHEN 'Data engineer' THEN 'Data Engineer'
        WHEN 'Research & Development role' THEN 'R&D'
        WHEN 'DevOps specialist' THEN 'DevOps'
        WHEN 'Data scientist or machine learning specialist' THEN 'Data Scientist / ML'
        WHEN 'Developer, back-end' THEN 'Backend Dev'
        ELSE DevType
      END as role,
      ROUND(MEDIAN(TRY_CAST(ConvertedCompYearly AS DOUBLE))) as salary
    FROM "sample_data"."stackoverflow_survey"."survey_results"
    WHERE year = '2024'
      AND DevType NOT IN ('NA','Other (please specify):')
      AND TRY_CAST(ConvertedCompYearly AS DOUBLE) BETWEEN 10000 AND 500000
      AND DevType IN (
        'Senior Executive (C-Suite, VP, etc.)', 'Engineering manager',
        'Cloud infrastructure engineer', 'Data engineer',
        'Research & Development role', 'DevOps specialist',
        'Data scientist or machine learning specialist', 'Developer, back-end'
      )
    GROUP BY DevType
    ORDER BY salary DESC
  `);
  const salaryData = Array.isArray(salaryQuery.data) ? salaryQuery.data : [];

  return (
    <div style={{ background: "#f8f8f8", minHeight: "100vh", fontFamily: "system-ui, sans-serif", color: "#231f20" }}>
      <div className="p-6">
        <h1 className="text-2xl font-bold mb-1">Stack Overflow Developer Survey</h1>
        <p className="text-sm mb-6" style={{ color: "#6a6a6a" }}>
          2017–2024 · {totalRespondents > 0 ? totalRespondents.toLocaleString() : "…"} total respondents across 8 years
        </p>

        {/* KPIs */}
        <div className="grid grid-cols-4 gap-8 mb-8">
          <div>
            {respondentsQuery.isLoading
              ? <div className="h-12 w-24 bg-gray-200 animate-pulse rounded" />
              : <p className="text-5xl font-bold" style={{ color: "#0777b3" }}>{latestCount.toLocaleString()}</p>}
            <p className="text-sm mt-2" style={{ color: "#6a6a6a" }}>2024 respondents</p>
          </div>
          <div>
            {aiQuery.isLoading
              ? <div className="h-12 w-24 bg-gray-200 animate-pulse rounded" />
              : <p className="text-5xl font-bold" style={{ color: "#0777b3" }}>{aiPct}%</p>}
            <p className="text-sm mt-2" style={{ color: "#6a6a6a" }}>using AI tools (2024)</p>
          </div>
          <div>
            <p className="text-5xl font-bold" style={{ color: "#0777b3" }}>8</p>
            <p className="text-sm mt-2" style={{ color: "#6a6a6a" }}>survey years</p>
          </div>
          <div>
            {salaryQuery.isLoading
              ? <div className="h-12 w-24 bg-gray-200 animate-pulse rounded" />
              : <p className="text-5xl font-bold" style={{ color: "#0777b3" }}>
                  ${(N(salaryData[0]?.salary) / 1000).toFixed(0)}K
                </p>}
            <p className="text-sm mt-2" style={{ color: "#6a6a6a" }}>top median salary (C-Suite)</p>
          </div>
        </div>

        {/* Language Trends */}
        <div className="mb-8">
          <h2 className="text-base font-semibold mb-1">Language Usage Trends (% of respondents)</h2>
          <p className="text-sm mb-3" style={{ color: "#6a6a6a" }}>Share of developers using each language, 2021–2024</p>
          <div className="flex gap-2 mb-3 flex-wrap">
            {LANGS.map((lang, i) => (
              <button
                key={lang}
                onClick={() => toggleLang(lang)}
                style={{
                  padding: "4px 10px", fontSize: "12px", borderRadius: "4px", cursor: "pointer",
                  border: `1px solid ${PALETTE[i]}`,
                  background: activeLangs.has(lang) ? PALETTE[i] : "transparent",
                  color: activeLangs.has(lang) ? "#fff" : PALETTE[i],
                  fontWeight: 500
                }}
              >{lang}</button>
            ))}
          </div>
          {langQuery.isLoading
            ? <div className="bg-gray-100 animate-pulse rounded" style={{ height: 220 }} />
            : <ResponsiveContainer width="100%" height={220}>
                <LineChart data={langTrendData}>
                  <CartesianGrid strokeDasharray="3 3" stroke="#eee" />
                  <XAxis dataKey="year" fontSize={11} />
                  <YAxis tickFormatter={(v) => `${v.toFixed(0)}%`} fontSize={11} domain={[8, 28]} />
                  <Tooltip formatter={(v, name) => [`${N(v).toFixed(1)}%`, name]} />
                  {LANGS.map((lang, i) =>
                    activeLangs.has(lang) ? (
                      <Line key={lang} type="linear" dataKey={lang} stroke={PALETTE[i]} strokeWidth={2} dot={{ r: 3 }} />
                    ) : null
                  )}
                </LineChart>
              </ResponsiveContainer>}
        </div>

        <div className="grid grid-cols-2 gap-8">
          {/* AI Adoption */}
          <div>
            <h2 className="text-base font-semibold mb-1">AI Tool Adoption (2024)</h2>
            <p className="text-sm mb-3" style={{ color: "#6a6a6a" }}>Are developers using AI coding tools?</p>
            {aiQuery.isLoading
              ? <div className="bg-gray-100 animate-pulse rounded" style={{ height: 180 }} />
              : <ResponsiveContainer width="100%" height={180}>
                  <BarChart data={aiData} layout="vertical">
                    <CartesianGrid strokeDasharray="3 3" stroke="#eee" horizontal={false} />
                    <XAxis type="number" fontSize={11} tickFormatter={(v) => `${(v / 1000).toFixed(0)}K`} />
                    <YAxis type="category" dataKey="label" width={110} fontSize={11} />
                    <Tooltip formatter={(v) => [N(v).toLocaleString(), "Respondents"]} />
                    <Bar dataKey="cnt" radius={[0, 3, 3, 0]}>
                      {aiData.map((_, i) => (
                        <Cell key={i} fill={i === 0 ? "#0777b3" : i === 1 ? "#bc1200" : "#638CAD"} />
                      ))}
                    </Bar>
                  </BarChart>
                </ResponsiveContainer>}
          </div>

          {/* Salary by Role */}
          <div>
            <h2 className="text-base font-semibold mb-1">Median Salary by Role (2024)</h2>
            <p className="text-sm mb-3" style={{ color: "#6a6a6a" }}>USD, converted from local currency</p>
            {salaryQuery.isLoading
              ? <div className="bg-gray-100 animate-pulse rounded" style={{ height: 180 }} />
              : <ResponsiveContainer width="100%" height={180}>
                  <BarChart data={salaryData} layout="vertical">
                    <CartesianGrid strokeDasharray="3 3" stroke="#eee" horizontal={false} />
                    <XAxis type="number" fontSize={11} tickFormatter={salaryFmt} />
                    <YAxis type="category" dataKey="role" width={120} fontSize={10} />
                    <Tooltip formatter={(v) => [`$${N(v).toLocaleString()}`, "Median Salary"]} />
                    <Bar dataKey="salary" fill="#0777b3" radius={[0, 3, 3, 0]} />
                  </BarChart>
                </ResponsiveContainer>}
          </div>
        </div>
      </div>
    </div>
  );
}


Interactive dashboard covering 2017–2024 Stack Overflow Developer Survey data. Shows language usage trends (2021–2024) with toggleable filters, AI tool adoption rates (2024), and median salary by developer role (2024).

Stack Overflow Developer Survey

Using the sample_data database (specifically stackoverflow_survey.survey_results), create an interactive dive dashboard that visualizes trends across the multi-year Stack Overflow Developer Survey dataset.

Stack Overflow Developer Survey

AI Prompts Used

Comments