
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.

NYC Taxi Operations Dashboard

AI Prompts Used

Comments