
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.

NYC 311 Service Requests

AI Prompts Used

Comments