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


export const REQUIRED_DATABASES = [
  { type: 'share', path: 'md:_share/snotel_wa/f41b9aba-72e7-4904-a652-178b3f581608', alias: 'snotel_wa' }
];
const N = (v) => (v != null ? Number(v) : 0);

const DOY_LABELS = { 0:"Oct 1", 31:"Nov 1", 61:"Dec 1", 92:"Jan 1", 122:"Feb 1", 153:"Mar 1", 182:"Apr 1", 213:"May 1", 243:"Jun 1", 274:"Jul 1", 304:"Aug 1", 335:"Sep 1" };

const WY_COLORS = {
  2026: "#0777b3",
  2025: "#6a6a6a",
  2024: "#adadad",
  2023: "#c8c8c8",
  2022: "#e0e0e0",
};

const CustomTooltip = ({ active, payload, label }) => {
  if (!active || !payload?.length) return null;
  const month = Object.entries(DOY_LABELS).reverse().find(([d]) => label >= Number(d));
  const dayInMonth = label - Number(month[0]) + 1;
  return (
    <div style={{background:"#231f20",padding:"8px 12px",borderRadius:4,fontSize:11,color:"#fff",lineHeight:1.6}}>
      <div style={{fontWeight:600,marginBottom:2}}>{month[1].replace(" 1","")}{" "}{dayInMonth}</div>
      {payload.filter(p=>p.value>0).map((p,i)=>(
        <div key={i} style={{display:"flex",gap:12,justifyContent:"space-between"}}>
          <span style={{color:p.color}}>{p.dataKey==="median"?"Median":p.dataKey.replace("wy","WY")}</span>
          <span style={{fontWeight:600}}>{N(p.value).toFixed(1)}"</span>
        </div>
      ))}
    </div>
  );
};

export default function SNOTELDashboard() {
  const [view, setView] = useState("swe");

  const sweQuery = useSQLQuery(`
    WITH wy AS (
      SELECT *,
        CASE WHEN month(Date) >= 10 THEN year(Date)+1 ELSE year(Date) END as water_year,
        (Date - CASE WHEN month(Date) >= 10 THEN make_date(year(Date),10,1) ELSE make_date(year(Date)-1,10,1) END)::int as day_of_wy,
        "Snow Water Equivalent (in) Start of Day Values" as swe
      FROM "snotel_wa"."main"."snotel_clean"
    ),
    median_baseline AS (
      SELECT day_of_wy, median(swe) as median_swe
      FROM wy WHERE water_year BETWEEN 1991 AND 2020
      GROUP BY day_of_wy
    ),
    recent AS (
      SELECT water_year, day_of_wy, swe
      FROM wy
      WHERE water_year BETWEEN 2022 AND 2026
        AND day_of_wy % 3 = 0
    )
    SELECT r.water_year, r.day_of_wy, r.swe, m.median_swe
    FROM recent r
    LEFT JOIN median_baseline m ON r.day_of_wy = m.day_of_wy
    ORDER BY r.water_year, r.day_of_wy
  `);

  const summaryQuery = useSQLQuery(`
    WITH wy AS (
      SELECT *,
        CASE WHEN month(Date) >= 10 THEN year(Date)+1 ELSE year(Date) END as water_year,
        (Date - CASE WHEN month(Date) >= 10 THEN make_date(year(Date),10,1) ELSE make_date(year(Date)-1,10,1) END)::int as day_of_wy,
        "Snow Water Equivalent (in) Start of Day Values" as swe,
        "Precipitation Accumulation (in) Start of Day Values" as precip_accum
      FROM "snotel_wa"."main"."snotel_clean"
    ),
    median_baseline AS (
      SELECT day_of_wy, median(swe) as median_swe
      FROM wy WHERE water_year BETWEEN 1991 AND 2020
      GROUP BY day_of_wy
    ),
    latest AS (
      SELECT * FROM wy WHERE water_year = (SELECT max(water_year) FROM wy) ORDER BY Date DESC LIMIT 1
    ),
    peak_years AS (
      SELECT water_year, max(swe) as peak_swe
      FROM wy WHERE water_year BETWEEN 2022 AND 2025
      GROUP BY water_year
    )
    SELECT
      strftime(l.Date, '%Y-%m-%d') as latest_date,
      l.swe as current_swe,
      l.day_of_wy as current_day_of_wy,
      m.median_swe as median_swe_today,
      CASE WHEN m.median_swe > 0 THEN round(l.swe / m.median_swe * 100, 1) ELSE 0 END as pct_of_median,
      l.precip_accum as precip_ytd,
      (SELECT avg(peak_swe) FROM peak_years) as avg_recent_peak
    FROM latest l
    LEFT JOIN median_baseline m ON l.day_of_wy = m.day_of_wy
  `);

  const recentQuery = useSQLQuery(`
    SELECT
      strftime(Date, '%b %d') as l,
      "Air Temperature Maximum (degF)" as hi,
      "Air Temperature Minimum (degF)" as lo,
      "Snow Water Equivalent (in) Start of Day Values" as swe,
      "Precipitation Increment (in)" as p
    FROM "snotel_wa"."main"."snotel_clean"
    WHERE Date BETWEEN (SELECT max(Date) - INTERVAL 30 DAY FROM "snotel_wa"."main"."snotel_clean") AND (SELECT max(Date) FROM "snotel_wa"."main"."snotel_clean")
    ORDER BY Date
  `);

  const peakQuery = useSQLQuery(`
    WITH wy AS (
      SELECT
        CASE WHEN month(Date) >= 10 THEN year(Date)+1 ELSE year(Date) END as water_year,
        "Snow Water Equivalent (in) Start of Day Values" as swe
      FROM "snotel_wa"."main"."snotel_clean"
    )
    SELECT water_year as wy, max(swe) as peak
    FROM wy
    WHERE water_year BETWEEN 2022 AND (SELECT max(CASE WHEN month(Date)>=10 THEN year(Date)+1 ELSE year(Date) END) FROM "snotel_wa"."main"."snotel_clean")
    GROUP BY water_year
    ORDER BY water_year
  `);

  const sweRows = Array.isArray(sweQuery.data) ? sweQuery.data : [];
  const summaryRows = Array.isArray(summaryQuery.data) ? summaryQuery.data : [];
  const recentRows = Array.isArray(recentQuery.data) ? recentQuery.data : [];
  const peakRows = Array.isArray(peakQuery.data) ? peakQuery.data : [];

  const summary = summaryRows[0] || {};

  const chartData = (() => {
    const byDay = {};
    sweRows.forEach(r => {
      const doy = N(r.day_of_wy);
      if (!byDay[doy]) byDay[doy] = { doy, median: N(r.median_swe) };
      byDay[doy][`wy${N(r.water_year)}`] = N(r.swe);
    });
    return Object.values(byDay).sort((a,b) => a.doy - b.doy);
  })();

  const Skeleton = ({h=48,w="6rem"}) => <div className="animate-pulse rounded" style={{height:h,width:w,background:"#e0e0e0"}} />;

  return (
    <div style={{background:"#f8f8f8",minHeight:"100%",fontFamily:"-apple-system,BlinkMacSystemFont,'Segoe UI',sans-serif"}}>
      <div style={{padding:"20px 24px 8px"}}>
        <h1 className="text-2xl font-semibold" style={{color:"#231f20",margin:0}}>SNOTEL WA — Snowpack</h1>
        <p className="text-sm" style={{color:"#6a6a6a",margin:"4px 0 0"}}>
          {summaryQuery.isLoading ? "Loading..." : `Water Year ${new Date().getMonth()>=9?new Date().getFullYear()+1:new Date().getFullYear()} · Data through ${summary.latest_date || "..."} · 1991–2020 baseline`}
        </p>
      </div>

      <div className="grid grid-cols-4 gap-6" style={{padding:"16px 24px"}}>
        <div>
          {summaryQuery.isLoading ? <Skeleton /> : <p className="text-4xl font-bold" style={{color:"#bc1200",margin:0}}>{N(summary.current_swe).toFixed(1)}"</p>}
          <p className="text-sm" style={{color:"#6a6a6a",margin:"4px 0 0"}}>Current SWE</p>
        </div>
        <div>
          {summaryQuery.isLoading ? <Skeleton /> : <p className="text-4xl font-bold" style={{color:"#bc1200",margin:0}}>{N(summary.pct_of_median).toFixed(0)}%</p>}
          <p className="text-sm" style={{color:"#6a6a6a",margin:"4px 0 0"}}>of Median ({N(summary.median_swe_today)}")</p>
        </div>
        <div>
          {summaryQuery.isLoading ? <Skeleton /> : <p className="text-4xl font-bold" style={{color:"#231f20",margin:0}}>{N(summary.precip_ytd)}"</p>}
          <p className="text-sm" style={{color:"#6a6a6a",margin:"4px 0 0"}}>Precip YTD</p>
        </div>
        <div>
          {summaryQuery.isLoading ? <Skeleton /> : <p className="text-4xl font-bold" style={{color:"#231f20",margin:0}}>{N(summary.avg_recent_peak).toFixed(0)}"</p>}
          <p className="text-sm" style={{color:"#6a6a6a",margin:"4px 0 0"}}>Avg Peak (4yr)</p>
        </div>
      </div>

      <div style={{display:"flex",gap:4,padding:"0 24px",borderBottom:"1px solid #e0e0e0"}}>
        {[["swe","SWE by Water Year"],["recent","30-Day Detail"],["peaks","Peak SWE History"]].map(([key,label])=>(
          <button key={key} onClick={()=>setView(key)} style={{
            padding:"8px 16px",fontSize:13,fontWeight:view===key?600:400,
            color:view===key?"#231f20":"#6a6a6a",background:"transparent",border:"none",cursor:"pointer",
            borderBottom:view===key?"2px solid #0777b3":"2px solid transparent",marginBottom:-1
          }}>{label}</button>
        ))}
      </div>

      <div style={{padding:"16px 24px 24px"}}>
        {view === "swe" && (
          <>
            <div style={{display:"flex",gap:16,alignItems:"center",marginBottom:12,flexWrap:"wrap"}}>
              {Object.entries(WY_COLORS).map(([wy,color])=>(
                <div key={wy} style={{display:"flex",alignItems:"center",gap:4,fontSize:11,color:"#6a6a6a"}}>
                  <div style={{width:16,height:2,background:color,borderRadius:1}} />
                  WY{wy}
                </div>
              ))}
              <div style={{display:"flex",alignItems:"center",gap:4,fontSize:11,color:"#6a6a6a"}}>
                <div style={{width:16,height:0,borderTop:"2px dashed #0777b3",opacity:0.4}} />
                Median
              </div>
            </div>
            {sweQuery.isLoading ? <div className="animate-pulse rounded" style={{height:280,background:"#e8e8e8"}} /> : (
              <ResponsiveContainer width="100%" height={280}>
                <LineChart data={chartData} margin={{top:5,right:10,bottom:5,left:0}}>
                  <CartesianGrid strokeDasharray="3 3" stroke="#eee" />
                  <XAxis dataKey="doy" fontSize={10} tickFormatter={v=>DOY_LABELS[v]||""} ticks={Object.keys(DOY_LABELS).map(Number).filter(d=>d<=300)} stroke="#ccc" />
                  <YAxis fontSize={10} tickFormatter={v=>`${v}"`} stroke="#ccc" />
                  <Tooltip content={<CustomTooltip/>} />
                  <Line type="linear" dataKey="median" stroke="#0777b3" strokeWidth={1.5} strokeDasharray="4 3" opacity={0.4} dot={false} />
                  <Line type="linear" dataKey="wy2022" stroke={WY_COLORS[2022]} strokeWidth={1} dot={false} />
                  <Line type="linear" dataKey="wy2023" stroke={WY_COLORS[2023]} strokeWidth={1} dot={false} />
                  <Line type="linear" dataKey="wy2024" stroke={WY_COLORS[2024]} strokeWidth={1} dot={false} />
                  <Line type="linear" dataKey="wy2025" stroke={WY_COLORS[2025]} strokeWidth={1.5} dot={false} />
                  <Line type="linear" dataKey="wy2026" stroke={WY_COLORS[2026]} strokeWidth={2.5} dot={false} />
                </LineChart>
              </ResponsiveContainer>
            )}
          </>
        )}

        {view === "recent" && (
          recentQuery.isLoading ? <div className="animate-pulse rounded" style={{height:280,background:"#e8e8e8"}} /> : (
            <ResponsiveContainer width="100%" height={280}>
              <ComposedChart data={recentRows.map(r=>({l:r.l,swe:N(r.swe),p:N(r.p)}))} margin={{top:5,right:10,bottom:5,left:0}}>
                <CartesianGrid strokeDasharray="3 3" stroke="#eee" />
                <XAxis dataKey="l" fontSize={9} interval={4} stroke="#ccc" />
                <YAxis yAxisId="swe" fontSize={10} tickFormatter={v=>`${v}"`} stroke="#ccc" />
                <YAxis yAxisId="precip" orientation="right" fontSize={10} tickFormatter={v=>`${v}"`} stroke="#ccc" />
                <Tooltip contentStyle={{background:"#231f20",border:"none",borderRadius:4,fontSize:11,color:"#fff"}} />
                <Bar yAxisId="precip" dataKey="p" fill="#0777b3" opacity={0.3} name="Daily Precip" />
                <Line yAxisId="swe" type="linear" dataKey="swe" stroke="#0777b3" strokeWidth={2} dot={false} name="SWE" />
              </ComposedChart>
            </ResponsiveContainer>
          )
        )}

        {view === "peaks" && (
          peakQuery.isLoading ? <div className="animate-pulse rounded" style={{height:280,background:"#e8e8e8"}} /> : (
            <ResponsiveContainer width="100%" height={280}>
              <BarChart data={peakRows.map(r=>({wy:N(r.wy),peak:N(r.peak)}))} margin={{top:5,right:10,bottom:5,left:0}}>
                <CartesianGrid strokeDasharray="3 3" stroke="#eee" />
                <XAxis dataKey="wy" fontSize={11} tickFormatter={v=>`WY${v}`} stroke="#ccc" />
                <YAxis fontSize={10} tickFormatter={v=>`${v}"`} stroke="#ccc" />
                <Tooltip contentStyle={{background:"#231f20",border:"none",borderRadius:4,fontSize:11,color:"#fff"}} formatter={(v)=>[`${N(v).toFixed(1)}"`, "Peak SWE"]} />
                <Bar dataKey="peak" fill="#0777b3" radius={[2,2,0,0]} />
              </BarChart>
            </ResponsiveContainer>
          )
        )}
      </div>
    </div>
  );
}

Washington state SNOTEL snowpack monitoring with multi-year SWE comparison (WY2022–2026), 30-year median baseline, 30-day detail view, and peak SWE history. Live queries against snotel_wa database.

SNOTEL WA Snowpack WY2026

garrett+devrel@motherduck.com


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


export const REQUIRED_DATABASES = [
  { type: 'share', path: 'md:_share/sample_data/23b0d623-1361-421d-ae77-62d701d471e6', alias: 'sample_data' }
];
const N = (v: unknown): number => (v != null ? Number(v) : 0);
const BLUE = "#0777b3";
const RED = "#bd4e35";
const GREEN = "#2d7a00";

export default function TaxiOpsDashboard() {
  const [selectedDay, setSelectedDay] = useState<string | null>(null);

  const kpis = useSQLQuery(`
    SELECT
      count(*) as total_trips,
      round(sum(total_amount), 0) as total_revenue,
      round(avg(trip_distance), 1) as avg_distance_mi,
      round(avg(total_amount), 2) as avg_fare
    FROM "sample_data"."nyc"."taxi"
    WHERE tpep_pickup_datetime >= '2022-11-01' AND tpep_pickup_datetime < '2022-12-01'
      AND total_amount > 0 AND trip_distance > 0
  `);

  const daily = useSQLQuery(`
    SELECT
      strftime(d, '%Y-%m-%d') as day,
      strftime(d, '%b %d') as day_label,
      COALESCE(count(t.tpep_pickup_datetime), 0) as trips,
      COALESCE(round(sum(t.total_amount), 0), 0) as revenue
    FROM generate_series(DATE '2022-11-01', DATE '2022-11-30', INTERVAL 1 DAY) AS s(d)
    LEFT JOIN "sample_data"."nyc"."taxi" t
      ON date_trunc('day', t.tpep_pickup_datetime) = s.d
      AND t.total_amount > 0 AND t.trip_distance > 0
    GROUP BY 1, 2 ORDER BY 1
  `);

  const hourly = useSQLQuery(`
    SELECT
      extract(hour FROM tpep_pickup_datetime) as hour,
      count(*) as trips
    FROM "sample_data"."nyc"."taxi"
    WHERE tpep_pickup_datetime >= '2022-11-01' AND tpep_pickup_datetime < '2022-12-01'
      AND total_amount > 0 AND trip_distance > 0
      ${selectedDay ? `AND strftime(tpep_pickup_datetime, '%Y-%m-%d') = '${selectedDay}'` : ""}
    GROUP BY 1 ORDER BY 1
  `);

  const topZones = useSQLQuery(`
    SELECT
      PULocationID as zone_id,
      count(*) as trips,
      round(avg(total_amount), 2) as avg_fare,
      round(avg(tip_amount / NULLIF(total_amount, 0) * 100), 1) as tip_pct
    FROM "sample_data"."nyc"."taxi"
    WHERE tpep_pickup_datetime >= '2022-11-01' AND tpep_pickup_datetime < '2022-12-01'
      AND total_amount > 0 AND trip_distance > 0
      ${selectedDay ? `AND strftime(tpep_pickup_datetime, '%Y-%m-%d') = '${selectedDay}'` : ""}
    GROUP BY 1 ORDER BY trips DESC LIMIT 8
  `);

  const kpiRows = Array.isArray(kpis.data) ? kpis.data : [];
  const dailyData = (Array.isArray(daily.data) ? daily.data : []).map(r => ({
    day: r.day as string,
    dayLabel: r.day_label as string,
    trips: N(r.trips),
    revenue: N(r.revenue),
  }));
  const hourlyData = (Array.isArray(hourly.data) ? hourly.data : []).map(r => ({
    hour: `${N(r.hour)}:00`,
    trips: N(r.trips),
  }));
  const zoneRows = (Array.isArray(topZones.data) ? topZones.data : []);

  return (
    <div className="p-6 space-y-6" style={{background:"#f8f8f8"}}>
      <div>
        <h1 className="text-2xl font-semibold" style={{color:"#231f20"}}>NYC Taxi Operations</h1>
        <p className="text-sm" style={{color:"#6a6a6a"}}>
          November 2022 {selectedDay ? `\u2014 ${selectedDay}` : ""}
          {selectedDay && (
            <button onClick={() => setSelectedDay(null)} className="ml-2 underline" style={{color:BLUE}}>
              clear filter
            </button>
          )}
        </p>
      </div>

      {/* KPIs */}
      <div className="grid grid-cols-4 gap-8">
        {[
          { label: "Total Trips", val: kpiRows[0]?.total_trips, fmt: (v: number) => v.toLocaleString() },
          { label: "Revenue", val: kpiRows[0]?.total_revenue, fmt: (v: number) => `$${(v / 1e6).toFixed(1)}M` },
          { label: "Avg Distance", val: kpiRows[0]?.avg_distance_mi, fmt: (v: number) => `${v} mi` },
          { label: "Avg Fare", val: kpiRows[0]?.avg_fare, fmt: (v: number) => `$${v.toFixed(2)}` },
        ].map(({ label, val, fmt }) => (
          <div key={label}>
            {kpis.isLoading ? (
              <div className="h-12 w-24 bg-gray-200 animate-pulse rounded" />
            ) : (
              <p className="text-4xl font-bold" style={{color:"#231f20"}}>{fmt(N(val))}</p>
            )}
            <p className="text-sm mt-1" style={{color:"#6a6a6a"}}>{label}</p>
          </div>
        ))}
      </div>

      {/* Daily trips — click a bar to filter */}
      <div>
        <h2 className="text-lg font-semibold mb-2" style={{color:"#231f20"}}>Daily Trip Volume</h2>
        <p className="text-xs mb-2" style={{color:"#6a6a6a"}}>Click a bar to filter hourly demand and top zones</p>
        {daily.isLoading ? (
          <div className="bg-gray-100 animate-pulse rounded" style={{height:200}} />
        ) : (
          <ResponsiveContainer width="100%" height={200}>
            <BarChart data={dailyData} onClick={(e) => { if (e?.activePayload?.[0]) setSelectedDay(e.activePayload[0].payload.day); }}>
              <CartesianGrid strokeDasharray="3 3" stroke="#eee" />
              <XAxis dataKey="dayLabel" fontSize={10} interval={2} />
              <YAxis tickFormatter={(v) => `${(v / 1000).toFixed(0)}K`} fontSize={10} />
              <Tooltip formatter={(v: number) => v.toLocaleString()} labelFormatter={(l) => `${l}`} />
              <Bar dataKey="trips" fill={BLUE} cursor="pointer" />
            </BarChart>
          </ResponsiveContainer>
        )}
      </div>

      {/* Bottom row: hourly demand + top zones */}
      <div className="grid grid-cols-2 gap-6">
        <div>
          <h2 className="text-lg font-semibold mb-2" style={{color:"#231f20"}}>Hourly Demand</h2>
          {hourly.isLoading ? (
            <div className="bg-gray-100 animate-pulse rounded" style={{height:200}} />
          ) : (
            <ResponsiveContainer width="100%" height={200}>
              <LineChart data={hourlyData}>
                <CartesianGrid strokeDasharray="3 3" stroke="#eee" />
                <XAxis dataKey="hour" fontSize={10} interval={3} />
                <YAxis tickFormatter={(v) => `${(v / 1000).toFixed(0)}K`} fontSize={10} />
                <Tooltip formatter={(v: number) => v.toLocaleString()} />
                <Line type="linear" dataKey="trips" stroke={RED} strokeWidth={2} dot={false} />
              </LineChart>
            </ResponsiveContainer>
          )}
        </div>

        <div>
          <h2 className="text-lg font-semibold mb-2" style={{color:"#231f20"}}>Top Pickup Zones</h2>
          {topZones.isLoading ? (
            <div className="animate-pulse space-y-2">
              {[...Array(5)].map((_, i) => <div key={i} className="h-4 bg-gray-200 rounded w-3/4" />)}
            </div>
          ) : (
            <table className="w-full text-sm" style={{color:"#231f20"}}>
              <thead>
                <tr style={{borderBottom:"1px solid #ddd"}}>
                  <th className="text-left py-1 font-semibold">Zone</th>
                  <th className="text-right py-1 font-semibold">Trips</th>
                  <th className="text-right py-1 font-semibold">Avg Fare</th>
                  <th className="text-right py-1 font-semibold">Tip %</th>
                </tr>
              </thead>
              <tbody>
                {zoneRows.map((r, i) => (
                  <tr key={i} style={{borderBottom:"1px solid #f0f0f0"}}>
                    <td className="py-1">Zone {N(r.zone_id)}</td>
                    <td className="text-right py-1">{N(r.trips).toLocaleString()}</td>
                    <td className="text-right py-1">${N(r.avg_fare).toFixed(2)}</td>
                    <td className="text-right py-1" style={{color: N(r.tip_pct) > 15 ? GREEN : "#231f20"}}>{N(r.tip_pct)}%</td>
                  </tr>
                ))}
              </tbody>
            </table>
          )}
        </div>
      </div>
    </div>
  );
}


Daily operations dashboard for NYC taxi fleet management — KPIs, daily trip volume, hourly demand patterns, and top pickup zones.

NYC Taxi Operations Dashboard

create a dive in my motherduck account based on taxi data in sample_data repo. show a dashboard that a logistics company might want to use to manage day-to-day taxi operations.

Ryan Boyd

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.

DevRel EU Central 1

Interactive NBA Game Quality explorer with Datadog-style key:value filter bar (Tab autocomplete, auto-close quotes, IN lists with bracket syntax, numeric comparisons, date part filters), edit/SQL toggle, 20x20 heatmap with contour edges and anchored axes, clickable axis label dropdowns, GQ box plot with player tick marks, orange dot overlay via player filter or dots:true directive, heatmap:true to apply filters to heatmap, sortable paginated table with click-to-filter rows.

NBA Game Quality Explorer — 2025-26

I have an NBA box scores share at md:_share/nba_box_scores/c9126ae3-ef30-4b6e-af8e-d2212c1f2797. It has tables box_scores_gq, box_scores, and schedule. Can you explore the schema of each table and show me what's in them? Then build a simple dive that queries individual player game performances for the 2025-26 season (games since 2025-10-20), joining box_scores_gq with box_scores (on game_id and entity_id, where period='FullGame') and schedule (on game_id). Show a sortable, paginated table with columns: Team, Player, Opponent, Date, MIN, PTS, 3PTM, FG%, FT%, REB, AST, STL, BLK, TO, and Game Quality (GQ). Sort by GQ descending by default. 50 rows per page.

Add a 20×20 bin heatmap above the table that plots two stats against each other — default to PTS (x-axis) vs GQ (y-axis). Use a blue gradient color scale (lighter = fewer games, darker = more). The axis bounds should be anchored to the full dataset so they don't shift when filters are applied. Add tick labels on both axes. Show a tooltip on hover with the game count for that cell. When you click a heatmap cell, it should cross-filter the table to only show games in that bin. Add a “clear” link to remove the cell selection.

Make the X and Y axis labels on the heatmap clickable. When clicked, show a dropdown menu that lets you switch the axis to any of these stats: PTS, REB, AST, STL, BLK, TO, 3PTM, FG_V, FT_V, GQ. The dropdown should highlight the currently selected stat and close when you click outside it. Changing an axis should clear any selected bin.

Add a narrow vertical box plot to the right of the heatmap showing the distribution of Game Quality scores (min, 25th percentile, median, 75th percentile, max). It should use the same data source as the heatmap and be vertically aligned with it.

Next to the filter bar, add small "edit" and "SQL" toggle links. In "edit" mode, show the normal editable filter input. In "SQL" mode, show a read-only rendering of the current filters translated to SQL WHERE clause syntax (e.g., WHERE team_id ILIKE '%LAL%' AND points >= 30). This helps users understand exactly what the filters are doing.

Replace any simple filter inputs with a single Datadog-style key:value text input filter bar. It should support these filter keys:

String filters (case-insensitive contains): team, player, opp/opponent, date
Numeric filters (supports >, >=, <, <=, =): pts, reb, ast, stl, blk, to, 3ptm, min, gq, fg%, ft%, fg_v, ft_v
Date part filters (numeric): year, month, day, week

Example usage: team:LAL player:"LeBron James" pts:>=30 month:>=12
Support quoted values for multi-word strings, IN-list syntax with brackets like team:[LAL,BOS,GSW], and multiple filters combined with AND logic. Add a clear (✕) button.

Polish the whole dive:

Title: "NBA Game Quality Explorer — 2025-26" with a subtitle explaining GQ is a head-to-head dominance score across 9 stat categories within each week
Add a color legend bar below the heatmap (Fewer → More games)
Show FG% and FT% in the table as 0.545 (+3.2) format showing the percentage and the value-over-expected
Add a help text footer listing all available filter keys with an example
Make sure the design is clean with system-ui font, light gray background, and consistent spacing

Add two more special filter directives:

heatmap:true — when present, the heatmap should reflect the currently filtered data instead of always showing the full dataset.
top:N — limits the result set to the top N rows by the current sort column/direction.

Also add contour edges to the heatmap — draw thin gray border lines along the edges where filled cells meet empty cells, to give the data shape a cleaner outline.

Add Tab-key autocomplete to the filter bar — when the user starts typing a key name and hits Tab, autocomplete it to the nearest matching key followed by a colon. Also add auto-close quotes: when the user types ", automatically insert a closing " and place the cursor between them. If the cursor is right before an existing closing quote, skip past it instead of inserting another pair.

Make table rows clickable. Clicking a row should toggle a player:"Player Name" filter in the filter bar. If that player is already filtered, remove the filter. This creates a quick way to drill into a specific player's games.

When a player: filter is active, overlay orange dots on the heatmap showing where that player's filtered games fall (with jitter so overlapping dots spread within a cell). Also add horizontal orange tick marks on the GQ box plot for those games. In the table, highlight matching player rows with a light orange background. Additionally, support a dots:true directive in the filter bar that shows dots for ALL currently filtered games (not just player-filtered ones).

Jacob Matson


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


export const REQUIRED_DATABASES = [
  { type: 'share', path: 'md:_share/sample_data/23b0d623-1361-421d-ae77-62d701d471e6', alias: 'sample_data' }
];
const N = (v) => (v != null ? Number(v) : 0);

export default function NYC311Dashboard() {
  const totalQuery = useSQLQuery(`SELECT COUNT(*) as total, COUNT(DISTINCT complaint_type) as types FROM "sample_data"."nyc"."service_requests"`);
  const trendQuery = useSQLQuery(`SELECT strftime(date_trunc('month', created_date), '%Y-%m') as month, COUNT(*) as complaints FROM "sample_data"."nyc"."service_requests" WHERE created_date >= '2020-01-01' AND created_date < '2023-01-01' GROUP BY 1 ORDER BY 1`);
  const boroughQuery = useSQLQuery(`SELECT borough, COUNT(*) as cnt FROM "sample_data"."nyc"."service_requests" WHERE borough IS NOT NULL AND borough != 'Unspecified' GROUP BY 1 ORDER BY 2 DESC`);
  const complaintsQuery = useSQLQuery(`SELECT complaint_type, COUNT(*) as cnt FROM "sample_data"."nyc"."service_requests" GROUP BY 1 ORDER BY 2 DESC LIMIT 8`);

  const totalRows = Array.isArray(totalQuery.data) ? totalQuery.data : [];
  const trendRows = Array.isArray(trendQuery.data) ? trendQuery.data : [];
  const boroughRows = Array.isArray(boroughQuery.data) ? boroughQuery.data : [];
  const complaintRows = Array.isArray(complaintsQuery.data) ? complaintsQuery.data : [];

  const chartTrend = trendRows.map(r => ({ month: r.month, complaints: N(r.complaints) }));
  const chartComplaints = complaintRows.map(r => ({
    name: String(r.complaint_type).length > 18 ? String(r.complaint_type).slice(0, 18) + "…" : String(r.complaint_type),
    count: N(r.cnt),
  }));

  return (
    <div className="p-6" style={{ background: "#f8f8f8", margin: "0 auto" }}>
      <h1 className="text-2xl font-semibold mt-2" style={{ color: "#231f20" }}>NYC 311 Service Requests</h1>
      <p className="text-sm mb-6" style={{ color: "#6a6a6a" }}>32.5M complaints from 2010–2023</p>

      {/* KPIs */}
      <div className="grid grid-cols-3 gap-8 mb-8">
        <div>
          {totalQuery.isLoading ? (
            <div className="h-12 w-24 bg-gray-200 animate-pulse rounded" />
          ) : (
            <p className="text-5xl font-bold" style={{ color: "#231f20" }}>{(N(totalRows[0]?.total) / 1e6).toFixed(1)}M</p>
          )}
          <p className="text-sm mt-2" style={{ color: "#6a6a6a" }}>Total Requests</p>
        </div>
        <div>
          {totalQuery.isLoading ? (
            <div className="h-12 w-24 bg-gray-200 animate-pulse rounded" />
          ) : (
            <p className="text-5xl font-bold" style={{ color: "#231f20" }}>{N(totalRows[0]?.types)}</p>
          )}
          <p className="text-sm mt-2" style={{ color: "#6a6a6a" }}>Complaint Types</p>
        </div>
        <div>
          <p className="text-5xl font-bold" style={{ color: "#231f20" }}>5</p>
          <p className="text-sm mt-2" style={{ color: "#6a6a6a" }}>Boroughs</p>
        </div>
      </div>

      {/* Monthly trend */}
      <p className="text-sm font-semibold mb-2" style={{ color: "#231f20" }}>Monthly Complaints (2020–2022)</p>
      {trendQuery.isLoading ? (
        <div className="bg-gray-100 animate-pulse rounded" style={{ height: 200 }} />
      ) : (
        <ResponsiveContainer width="100%" height={200}>
          <LineChart data={chartTrend}>
            <CartesianGrid strokeDasharray="3 3" stroke="#eee" />
            <XAxis dataKey="month" fontSize={10} interval={5} />
            <YAxis tickFormatter={(v) => `${(v / 1000).toFixed(0)}K`} fontSize={10} />
            <Tooltip formatter={(v) => v.toLocaleString()} />
            <Line type="linear" dataKey="complaints" stroke="#0777b3" strokeWidth={2} dot={false} />
          </LineChart>
        </ResponsiveContainer>
      )}

      {/* Borough + Top Complaints side by side */}
      <div className="grid grid-cols-2 gap-6 mt-6">
        <div>
          <p className="text-sm font-semibold mb-2" style={{ color: "#231f20" }}>By Borough</p>
          {boroughQuery.isLoading ? (
            <div className="animate-pulse space-y-2">
              <div className="h-4 bg-gray-200 rounded w-3/4" />
              <div className="h-4 bg-gray-200 rounded w-1/2" />
              <div className="h-4 bg-gray-200 rounded w-2/3" />
            </div>
          ) : (
            <table className="w-full text-sm">
              <tbody>
                {boroughRows.map((r) => (
                  <tr key={r.borough} style={{ borderBottom: "1px solid #eee" }}>
                    <td className="py-1" style={{ color: "#231f20" }}>{r.borough}</td>
                    <td className="py-1 text-right font-medium" style={{ color: "#231f20" }}>{(N(r.cnt) / 1e6).toFixed(1)}M</td>
                  </tr>
                ))}
              </tbody>
            </table>
          )}
        </div>
        <div>
          <p className="text-sm font-semibold mb-2" style={{ color: "#231f20" }}>Top Complaints</p>
          {complaintsQuery.isLoading ? (
            <div className="bg-gray-100 animate-pulse rounded" style={{ height: 180 }} />
          ) : (
            <ResponsiveContainer width="100%" height={180}>
              <BarChart data={chartComplaints} layout="vertical" margin={{ left: 80 }}>
                <XAxis type="number" tickFormatter={(v) => `${(v / 1e6).toFixed(0)}M`} fontSize={10} />
                <YAxis type="category" dataKey="name" fontSize={10} width={80} />
                <Tooltip formatter={(v) => v.toLocaleString()} />
                <Bar dataKey="count" fill="#0777b3" radius={[0, 2, 2, 0]} />
              </BarChart>
            </ResponsiveContainer>
          )}
        </div>
      </div>
    </div>
  );
}


Dashboard of 32.5M NYC 311 complaints (2010–2023) showing monthly trends, borough breakdown, and top complaint types.