Motherduck Build Dashboard

Build an Analytics Dashboard

Use this skill when the user wants a multi-section Dive-backed dashboard with a clear analytical story, not just a single chart.

This is a use-case skill. It orchestrates motherduck-explore, motherduck-query, and motherduck-create-dive; use motherduck-duckdb-sql as supporting reference when exact syntax matters.

Start Here: Is a MotherDuck Server Active?

Always determine this before designing the dashboard.

• If a remote MotherDuck MCP server or local MotherDuck server is active, use it.
• If the target database is unclear, ask which database or workspace the dashboard should run against.
• Explore the live data model before choosing the dashboard structure:
• available tables and views
• business grain
• key metrics
• key dimensions
• date columns
• likely joins

The discovered data model should determine the dashboard story and sections.

If no server is active, ask for a table list or schema excerpt and make the assumptions visible.

Use This Skill When

• The user wants KPIs plus trend and breakdown views in one artifact.
• The result should be a saved, shareable Dive.
• The work needs dashboard composition, not just chart mechanics.
• The result is a workspace analytics surface, not a customer-facing product backend.

For lower-level Dive mechanics, use motherduck-create-dive.

Dashboard Defaults

• One story per dashboard.
• One KPI row.
• One primary trend chart.
• Zero or one supporting chart.
• Zero or one detail table.
• Heavy shaping in SQL, not React.

Workflow

1. Confirm whether live MotherDuck discovery is available. 2. Explore the real schema and metrics first. 3. Pick the dashboard story. 4. Write one query per section. 5. Compose the dashboard in a Dive. When MotherDuck MCP is available, call get_dive_guide before save_dive or update_dive. 6. Save only after preview iteration is approved.

When this skill produces a native DuckDB (md:) connection, watermark it with custom_user_agent=agent-skills/2.3.0(harness-<harness>;llm-<llm>). If metadata is missing, fall back to harness-unknown and llm-unknown.

Output

The output of this skill should be:

• the dashboard story
• the section list
• the validated SQL for each section
• the Dive implementation plan
• the save/update path

If the caller explicitly asks for structured JSON, return raw JSON only with no Markdown fences or prose before/after it. This is mainly for automated tests, regression checks, or downstream tooling that needs a stable machine-readable shape. Normal human-facing use of the skill can stay in prose unless JSON is explicitly requested.

Use this exact top-level shape when JSON is requested:

{
  "summary": {},
  "assumptions": [],
  "implementation_plan": [],
  "validation_plan": [],
  "risks": []
}

References

• references/DASHBOARD_IMPLEMENTATION_GUIDE.md -- preserved detailed workflow and layout guidance that used to live in this skill
• references/DASHBOARD_PATTERNS.md -- example dashboard compositions and reusable sections

Runnable Artifact

• artifacts/dashboard_story_example.py -- MotherDuck-backed Python example that produces KPI, trend, breakdown, and detail outputs for one dashboard story
• artifacts/dashboard_story_example.ts -- TypeScript companion artifact with the same dashboard output contract

Run it with:

uv run --with duckdb python skills/motherduck-build-dashboard/artifacts/dashboard_story_example.py

Run the same artifact against a temporary MotherDuck database:

MOTHERDUCK_ARTIFACT_USE_MOTHERDUCK=1 \
uv run --with duckdb python skills/motherduck-build-dashboard/artifacts/dashboard_story_example.py

Validate the TypeScript companion artifact:

uv run scripts/test_typescript_artifacts.py

Related Skills

• motherduck-explore -- inspect the actual database before deciding the dashboard sections
• motherduck-query -- validate each dashboard query
• motherduck-create-dive -- useSQLQuery, theming, preview/save, loading, and visual mechanics
• motherduck-duckdb-sql -- resolve syntax and function questions

<!-- Preserved detailed implementation guidance moved from SKILL.md so the main skill can stay concise. -->

Build an Analytics Dashboard

Use this skill when creating a multi-chart, multi-KPI interactive dashboard with live MotherDuck data. This is a use-case skill -- it ties together motherduck-explore, motherduck-query, motherduck-create-dive, and motherduck-duckdb-sql into a single end-to-end workflow.

Contents

• Source Of Truth
• Verified Delivery Defaults
• Validation Signals
• Language Focus: TypeScript/Javascript and Python
• TypeScript/TSX Starter
• Python Validation Starter
• When to Use
• Prerequisites
• Dashboard Workflow
• Dashboard Design Principles
• Key Rules
• Common Mistakes
• Related Skills

Source Of Truth

• Prefer the current MotherDuck Dive guide and public Dives docs first.
• If MotherDuck MCP is available, call get_dive_guide before saving or updating a dashboard Dive.
• Keep the dashboard guidance aligned with the documented product posture:
• Dives are for live workspace analytics and the long tail of questions
• heavy shaping belongs in SQL, not in React
• small previews are for iteration; saved dashboards should query live data
• for full customer-facing analytics with per-customer isolation, see motherduck-build-cfa-app

Verified Delivery Defaults

The repeated repo runs point to a stable dashboard posture:

• keep one dashboard story per Dive instead of mixing several unrelated narratives
• shape metrics and breakdowns in SQL first, then render the result in TSX
• use small previews for iteration, but keep saved dashboards live against MotherDuck data
• escalate to motherduck-build-cfa-app when the request becomes a customer-facing product surface rather than a workspace dashboard

Validation Signals

Use these signals for testing, review, and regression checks. They are not an instruction to include a separate "Validation Signals" section in normal user-facing replies.

• run artifacts/dashboard_story_example.py against a temporary MotherDuck database
• verify the output contains the expected sections: kpis, trend, breakdown, and detail
• verify the dashboard still tells one coherent story instead of several unrelated narratives
• treat dashboard plans without explicit section-to-SQL mapping as incomplete

Language Focus: TypeScript/Javascript and Python

• Prefer TypeScript/TSX for dashboard UI examples because Dives are React components.
• Prefer Python for:
• preparing the source dataset
• validating aggregations before visualization
• automating dashboard refresh or publication workflows outside the Dive code
• The normal split is:
• SQL for metrics and aggregation
• TypeScript/TSX for rendering
• Python only when data prep or validation is part of the task

TypeScript/TSX Starter

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

const N = (v: unknown): number => (v != null ? Number(v) : 0);

export default function MonthlyRevenueDashboard() {
  const kpis = useSQLQuery(`
    SELECT SUM(revenue) AS total_revenue,
           COUNT(DISTINCT order_id) AS order_count,
           ROUND(AVG(revenue), 2) AS avg_order_value
    FROM "analytics"."main"."orders"
  `);

  const trend = useSQLQuery(`
    SELECT strftime(date_trunc('month', order_date), '%Y-%m') AS month,
           SUM(revenue) AS revenue
    FROM "analytics"."main"."orders"
    GROUP BY 1 ORDER BY 1
  `);

  const kpiRows = Array.isArray(kpis.data) ? kpis.data : [];
  const trendData = (Array.isArray(trend.data) ? trend.data : []).map(r => ({
    month: r.month as string,
    revenue: N(r.revenue),
  }));

  return (
    <div className="p-6" style={{ background: "#f8f8f8" }}>
      <h1 className="text-2xl font-semibold" style={{ color: "#231f20" }}>Revenue</h1>
      <p className="text-sm mb-6" style={{ color: "#6a6a6a" }}>Monthly overview</p>

      <div className="grid grid-cols-3 gap-8 mb-8">
        {[
          { label: "Total Revenue", value: kpiRows[0]?.total_revenue, fmt: (v: number) => `$${(v / 1000).toFixed(0)}K` },
          { label: "Orders", value: kpiRows[0]?.order_count, fmt: (v: number) => v.toLocaleString() },
          { label: "Avg Order", value: kpiRows[0]?.avg_order_value, fmt: (v: number) => `$${v.toFixed(2)}` },
        ].map(({ label, value, fmt }) => (
          <div key={label}>
            {kpis.isLoading ? (
              <div className="h-12 w-24 bg-gray-200 animate-pulse rounded" />
            ) : (
              <p className="text-5xl font-bold" style={{ color: "#231f20" }}>{fmt(N(value))}</p>
            )}
            <p className="text-sm mt-2" style={{ color: "#6a6a6a" }}>{label}</p>
          </div>
        ))}
      </div>

      {trend.isLoading ? (
        <div className="bg-gray-100 animate-pulse rounded" style={{ height: 250 }} />
      ) : (
        <ResponsiveContainer width="100%" height={250}>
          <LineChart data={trendData}>
            <CartesianGrid strokeDasharray="3 3" stroke="#eee" />
            <XAxis dataKey="month" fontSize={11} />
            <YAxis tickFormatter={(v) => `$${(v / 1000).toFixed(0)}K`} fontSize={11} />
            <Tooltip formatter={(v: number) => `$${v.toLocaleString()}`} />
            <Line type="linear" dataKey="revenue" stroke="#0777b3" strokeWidth={2} dot={false} />
          </LineChart>
        </ResponsiveContainer>
      )}
    </div>
  );
}

Python Validation Starter

import duckdb

USE_CASE_USER_AGENT = "agent-skills/2.3.0(harness-<harness>;llm-<llm>)"

conn = duckdb.connect(f"md:analytics?custom_user_agent={USE_CASE_USER_AGENT}")
rows = conn.sql("""
SELECT strftime(date_trunc('month', order_date), '%Y-%m') AS month,
       SUM(revenue) AS revenue
FROM "analytics"."main"."orders"
GROUP BY 1
ORDER BY 1
""").fetchall()
conn.close()

When to Use

• The user asks for a dashboard, report, or multi-section data app.
• The output requires more than a single chart -- typically KPIs, trend charts, breakdowns, and detail tables combined.
• The data lives in MotherDuck and the result should be a saved, shareable Dive.
• The request is a workspace analytics surface. For full customer-facing apps with per-customer isolation, see motherduck-build-cfa-app.

Prerequisites

• Data must already exist in MotherDuck. Use motherduck-explore to discover databases and tables before starting.
• Familiarity with motherduck-create-dive skill for Dive mechanics (useSQLQuery, N() helper, Recharts, Tailwind, loading states).

---

Dashboard Workflow

Follow these six steps in order. Do not skip steps -- each one depends on the output of the previous step.

For implementation:

• prefer TypeScript/TSX for the dashboard UI because Dives are React components
• prefer Python for validating the source metrics before the UI is written
• do not move grouping, filtering, or date formatting out of SQL just because the UI is in TypeScript

Step 1: Explore Available Data

Use the motherduck-explore skill to discover what data is available and understand its shape.

1. List databases with MD_ALL_DATABASES(). 2. List tables in the target database with duckdb_tables(). 3. Inspect columns with duckdb_columns() to understand types and nullability. 4. Run SUMMARIZE on each key table to understand distributions, ranges, null rates, and cardinality. 5. Check date ranges on every time column -- the data may not cover the period you expect, which changes the dashboard story entirely. 6. Sample rows with LIMIT 10 to see actual values.

A quick date range check prevents building a dashboard on stale or misaligned data:

SELECT min(order_date) AS earliest,
       max(order_date) AS latest,
       count(*) AS total_rows
FROM "my_db"."main"."orders";

Identify the following before proceeding:

• Key metrics -- the numeric columns that will become KPIs and chart values (e.g., revenue, order count, session duration).
• Key dimensions -- the categorical or temporal columns used for grouping, filtering, and axis labels (e.g., category, region, date).
• Date/time columns -- the timestamps used for time-series trends.
• Relationships -- how tables join together (shared keys like customer_id, product_id).

Do not proceed to Step 2 until you can name the exact columns you will query.

---

Step 2: Define the Dashboard Story

Every dashboard tells ONE story. Pick a single narrative focus before writing any code.

Common dashboard stories:

• Revenue and sales performance
• Product usage and engagement
• Operational efficiency and reliability
• Customer behavior and retention

Define the sections:

1. KPIs (3-5 numbers). These are the most important metrics at a glance. Pick the numbers the user would check first every morning. Examples: Total Revenue, Order Count, Average Order Value, Customer Count.

2. Primary chart (1 required). This shows the main trend -- usually a time-series. Examples: Monthly Revenue (LineChart), Daily Active Users (AreaChart), Weekly Request Volume (AreaChart).

3. Secondary chart (0-1 optional). This shows a breakdown or comparison. Examples: Revenue by Category (BarChart), Error Rate by Endpoint (BarChart), Feature Usage (BarChart).

4. Detail table (0-1 optional). Use a table when the user needs exact values or when there are more than 8 categories. Examples: Top 10 Products by Revenue, Slowest Endpoints, Top Pages by Views.

Constraints:

• Maximum 5 KPIs.
• Maximum 2 charts.
• Maximum 1 table.
• If you find yourself adding more, split into multiple dashboards instead.

---

Step 3: Write the SQL Queries

Write one useSQLQuery call per dashboard section. Separate queries ensure independent loading states and keep each query simple and debuggable.

Query design rules:

1. One query per section. KPIs get one query. Each chart gets its own query. The table gets its own query.

2. Pre-aggregate in SQL, not JavaScript. Compute sums, averages, counts, and ratios in SQL. The React component should only render values, never compute them.

3. Format dates in SQL. Use strftime(date_trunc('month', ts), '%Y-%m') or strftime(date_trunc('day', ts), '%Y-%m-%d'). Never parse or format dates in JavaScript.

4. Use fully qualified table names. Always reference tables as "database"."schema"."table".

5. Order and limit in SQL. Sort time-series data with ORDER BY 1. Limit detail tables with LIMIT 10 or LIMIT 20.

6. Use CTEs for complex logic. Break multi-step calculations into CTEs for readability.

7. Preview cheaply, save live. Use small subsets or aggregates while iterating, then keep the final saved Dive wired to live useSQLQuery calls.

Example queries for a sales dashboard:

-- KPI query: returns one row with all KPI values
SELECT SUM(revenue) AS total_revenue,
       COUNT(DISTINCT order_id) AS order_count,
       ROUND(AVG(revenue), 2) AS avg_order_value,
       COUNT(DISTINCT customer_id) AS customer_count
FROM "my_db"."main"."orders"

-- Trend query: monthly revenue for a line chart
SELECT strftime(date_trunc('month', order_date), '%Y-%m') AS month,
       SUM(revenue) AS revenue
FROM "my_db"."main"."orders"
GROUP BY 1 ORDER BY 1

-- Breakdown query: revenue by category for a bar chart
SELECT category, SUM(revenue) AS revenue
FROM "my_db"."main"."orders"
GROUP BY 1 ORDER BY 2 DESC LIMIT 8

-- Detail query: top products for a table
SELECT product_name, category,
       SUM(revenue) AS revenue, COUNT(*) AS orders
FROM "my_db"."main"."order_items"
GROUP BY 1, 2 ORDER BY 3 DESC LIMIT 10

Use the motherduck-query skill to test each query against real data before embedding it in the Dive.

---

Step 4: Design the Layout

Follow these layout conventions for a consistent, professional dashboard.

Structure (top to bottom):

1. Title -- text-2xl font-bold mb-8 with color: "#231f20". 2. KPI row -- grid grid-cols-4 gap-8 mb-10 (use grid-cols-3 or grid-cols-5 if needed). 3. Primary chart -- full width, 200-280px height, mb-10. 4. Secondary chart -- full width, 200-280px height, mb-10 (optional). 5. Detail table -- full width with overflow-x-auto (optional).

Styling rules:

• Outermost container: className="p-8 min-h-screen" with style={{ backgroundColor: "#f8f8f8" }}.
• No card borders, no card shadows. Content floats on the background.
• KPI labels: text-sm with color: "#6a6a6a".
• KPI values: text-5xl font-bold with color: "#231f20".
• Section headings: text-lg font-semibold mb-4 with color: "#231f20".
• Use inline style for brand colors. Never use Tailwind bracket syntax (w-[200px]).

Color palette for charts:

const COLORS = ["#0777b3", "#bd4e35", "#2d7a00", "#e18727", "#638CAD", "#adadad"];

Use colors consistently across all charts. The primary series always uses #0777b3.

---

Step 5: Build the Dive

Assemble the React component using motherduck-create-dive skill patterns.

Component structure:

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

const N = (v: unknown): number => (v != null ? Number(v) : 0);
const COLORS = ["#0777b3", "#bd4e35", "#2d7a00", "#e18727", "#638CAD", "#adadad"];

export default function MyDashboard() {
  // Separate queries for each dashboard section
  const { data: kpiData, isLoading: kpiLoading } = useSQLQuery(`SELECT ... -- KPIs`);
  const kpiRows = Array.isArray(kpiData) ? kpiData : [];

  const { data: trendData, isLoading: trendLoading } = useSQLQuery(`SELECT ... -- Time series`);
  const trendRows = Array.isArray(trendData) ? trendData : [];

  const { data: breakdownData, isLoading: breakdownLoading } = useSQLQuery(`SELECT ... -- Breakdown`);
  const breakdownRows = Array.isArray(breakdownData) ? breakdownData : [];

  const { data: detailData, isLoading: detailLoading } = useSQLQuery(`SELECT ... -- Detail table`);
  const detailRows = Array.isArray(detailData) ? detailData : [];

  return (
    <div className="p-8 min-h-screen" style={{ backgroundColor: "#f8f8f8" }}>
      {/* Title */}
      {/* KPIs with kpiLoading skeleton */}
      {/* Primary chart with trendLoading spinner */}
      {/* Secondary chart with breakdownLoading spinner */}
      {/* Detail table with detailLoading skeleton */}
    </div>
  );
}

Dive component mechanics -- export default function, the N() helper, Array.isArray guards, per-section loading skeletons and spinners, ResponsiveContainer -- are owned by motherduck-create-dive. Follow that skill's rules; references/DASHBOARD_PATTERNS.md shows them applied in complete dashboard templates.

The dashboard-specific rule: each section renders its own loading state independently. Never use a single full-page spinner.

Create the Dive via MD_CREATE_DIVE (SQL) or save_dive (MCP). When MCP is available, call get_dive_guide first.

---

Step 6: Iterate

After the initial Dive is created:

1. Open the Dive at the returned URL. 2. Verify that all sections load with real data. 3. Check that KPI values are reasonable and formatted correctly. 4. Confirm charts display the expected trends and categories. 5. Update via MD_UPDATE_DIVE_CONTENT (SQL) or update_dive (MCP) to fix issues.

Common iteration fixes:

• Adjust date truncation granularity (day vs. week vs. month).
• Change chart type (LineChart to AreaChart, or BarChart to table).
• Tune LIMIT values for breakdown charts and detail tables.
• Add or remove KPIs based on user feedback.

---

Dashboard Design Principles

1. Start with KPIs. The most important numbers appear at the top. A user should understand the current state of the business in the first 2 seconds.

2. One chart shows the primary trend. This is almost always a time-series (LineChart or AreaChart). It answers "how is the main metric changing over time?"

3. Second chart shows a breakdown or comparison. This is usually a BarChart. It answers "where is the main metric coming from?" or "how do segments compare?"

4. Tables for detail. Use a table when there are more than 8 categories or when the user needs exact values. Tables are clearer than bar charts with many bars.

5. One dashboard, one narrative. Do not mix unrelated stories (e.g., sales performance and server health) in one dashboard. Build separate dashboards instead.

6. Consistent colors across all charts. Use the same COLORS array for all charts. The primary series is always #0777b3.

7. Pre-aggregate everything in SQL. The React component formats and renders. It never computes aggregations, filters data, or transforms values.

---

Key Rules

• One dashboard = one story. Do not mix unrelated metrics.
• Max 5 KPIs, 2 charts, 1 table. More than this and the dashboard becomes noisy.
• Every section has independent loading. Each useSQLQuery manages its own isLoading state.
• Pre-aggregate in SQL, not JavaScript. The component renders values; it does not compute them.
• Format dates in SQL with `strftime()`. Never use new Date() or date parsing in JavaScript.
• Use fully qualified table names. Always "database"."schema"."table".
• Background `#f8f8f8`, no card borders, no card shadows.
• Follow `motherduck-create-dive` component rules. export default function, N() for all numeric query values, Array.isArray guards, no Tailwind bracket syntax.

---

Common Mistakes

1. Too many charts. The dashboard becomes noisy and loses focus. Limit to 2 charts maximum. If you need more, build a second dashboard.

2. One giant query instead of separate queries per section. Each section should have its own useSQLQuery call. One query for everything means one loading state for everything -- the dashboard feels slow and errors cascade.

3. Formatting and computing in JavaScript instead of SQL. Compute sums, averages, ratios, and date formatting in SQL. The React component only renders the pre-computed values.

4. Inconsistent colors across charts. Define COLORS once and use the same array for every chart. Do not pick ad-hoc colors.

5. Missing loading states. Every section needs its own loading skeleton or spinner. A blank section while data loads looks broken.

6. Forgetting the `N()` helper. Query values are unknown. Without N(), numeric operations return NaN and charts render blank.

7. Parsing dates in JavaScript. Use strftime() in SQL. JavaScript new Date() parsing is unreliable and causes timezone bugs.

8. Not guarding data with `Array.isArray`. Calling .map() on undefined during the loading phase crashes the entire Dive.

9. Using Tailwind bracket syntax. w-[200px] and text-[#333] do not work in Dives. Use inline style instead.

10. Card borders and shadows. The design system uses a flat #f8f8f8 background with no containers. Do not wrap sections in bordered cards.

---

Related Skills

• motherduck-explore -- Discover databases, tables, columns, and data shares.
• motherduck-query -- Execute and optimize analytical SQL queries against MotherDuck.
• motherduck-create-dive -- Visualization mechanics: useSQLQuery, Recharts, Tailwind, loading states.
• motherduck-duckdb-sql -- DuckDB SQL syntax reference and function lookup.

Dashboard Patterns

Copy-pasteable dashboard templates. Each is a complete Dive component with proper imports, independent loading states, N() helper, and the standard color palette. Replace placeholder table names with your actual fully qualified names.

Contents

• 1. Sales Dashboard
• 2. Product Analytics Dashboard
• 3. Operational Metrics Dashboard
• Adapting Templates

---

1. Sales Dashboard

KPIs: Total Revenue, Order Count, Avg Order Value, Customer Count. Charts: Monthly Revenue (Line), Revenue by Category (Bar). Table: Top 10 Products.

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

const N = (v: unknown): number => (v != null ? Number(v) : 0);
const COLORS = ["#0777b3", "#bd4e35", "#2d7a00", "#e18727", "#638CAD", "#adadad"];

export default function SalesDashboard() {
  const { data: kpiData, isLoading: kpiLoading, isError: kpiError, error: kpiMsg } = useSQLQuery(`
    SELECT SUM(revenue) AS total_revenue, COUNT(DISTINCT order_id) AS order_count,
           ROUND(AVG(revenue), 2) AS avg_order_value, COUNT(DISTINCT customer_id) AS customer_count
    FROM "my_db"."main"."orders"
  `);
  const kpiRows = Array.isArray(kpiData) ? kpiData : [];
  const { data: trendData, isLoading: trendLoading } = useSQLQuery(`
    SELECT strftime(date_trunc('month', order_date), '%Y-%m') AS month, SUM(revenue) AS revenue
    FROM "my_db"."main"."orders" GROUP BY 1 ORDER BY 1
  `);
  const trendRows = Array.isArray(trendData) ? trendData : [];
  const { data: catData, isLoading: catLoading } = useSQLQuery(`
    SELECT category, SUM(revenue) AS revenue
    FROM "my_db"."main"."order_items" GROUP BY 1 ORDER BY 2 DESC LIMIT 8
  `);
  const catRows = Array.isArray(catData) ? catData : [];
  const { data: detailData, isLoading: detailLoading } = useSQLQuery(`
    SELECT product_name, category, SUM(revenue) AS revenue, COUNT(*) AS orders
    FROM "my_db"."main"."order_items" GROUP BY 1, 2 ORDER BY 3 DESC LIMIT 10
  `);
  const detailRows = Array.isArray(detailData) ? detailData : [];

  const KPI = ({ label, value, prefix = "" }: {
    label: string; value: string; prefix?: string;
  }) => (
    <div>
      <p className="text-sm mb-1" style={{ color: "#6a6a6a" }}>{label}</p>
      {kpiLoading ? (
        <div className="h-12 w-24 bg-gray-200 animate-pulse rounded" />
      ) : (
        <p className="text-5xl font-bold" style={{ color: "#231f20" }}>{prefix}{value}</p>
      )}
    </div>
  );

  return (
    <div className="p-8 min-h-screen" style={{ backgroundColor: "#f8f8f8" }}>
      <h1 className="text-2xl font-bold mb-8" style={{ color: "#231f20" }}>Sales Dashboard</h1>
      {/* KPIs */}
      <div className="grid grid-cols-4 gap-8 mb-10">
        <KPI label="Total Revenue" prefix="$" value={`${(N(kpiRows[0]?.total_revenue) / 1000).toFixed(0)}K`} />
        <KPI label="Order Count" value={N(kpiRows[0]?.order_count).toLocaleString()} />
        <KPI label="Avg Order Value" prefix="$" value={N(kpiRows[0]?.avg_order_value).toFixed(2)} />
        <KPI label="Customers" value={N(kpiRows[0]?.customer_count).toLocaleString()} />
      </div>
      {kpiError && (
        <p className="text-sm mb-4" style={{ color: "#bd4e35" }}>
          Failed to load KPIs: {kpiMsg?.message || "Unknown error"}
        </p>
      )}
      {/* Chart 1: Monthly Revenue Trend */}
      <div className="mb-10">
        <h2 className="text-lg font-semibold mb-4" style={{ color: "#231f20" }}>Monthly Revenue</h2>
        {trendLoading ? (
          <div className="flex items-center justify-center h-64">
            <Loader2 className="animate-spin" size={32} style={{ color: "#0777b3" }} />
          </div>
        ) : (
          <ResponsiveContainer width="100%" height={260}>
            <LineChart data={trendRows}>
              <CartesianGrid strokeDasharray="3 3" stroke="#e0e0e0" />
              <XAxis dataKey="month" tick={{ fontSize: 12 }} />
              <YAxis tick={{ fontSize: 12 }} tickFormatter={(v) => `$${(v / 1000).toFixed(0)}K`} />
              <Tooltip formatter={(value: number) => `$${value.toLocaleString()}`} />
              <Line type="monotone" dataKey="revenue" stroke={COLORS[0]} strokeWidth={2} dot={false} />
            </LineChart>
          </ResponsiveContainer>
        )}
      </div>
      {/* Chart 2: Revenue by Category */}
      <div className="mb-10">
        <h2 className="text-lg font-semibold mb-4" style={{ color: "#231f20" }}>Revenue by Category</h2>
        {catLoading ? (
          <div className="flex items-center justify-center h-64">
            <Loader2 className="animate-spin" size={32} style={{ color: "#0777b3" }} />
          </div>
        ) : (
          <ResponsiveContainer width="100%" height={260}>
            <BarChart data={catRows}>
              <CartesianGrid strokeDasharray="3 3" stroke="#e0e0e0" />
              <XAxis dataKey="category" tick={{ fontSize: 12 }} />
              <YAxis tick={{ fontSize: 12 }} tickFormatter={(v) => `$${(v / 1000).toFixed(0)}K`} />
              <Tooltip formatter={(value: number) => `$${value.toLocaleString()}`} />
              <Bar dataKey="revenue" fill={COLORS[0]} radius={[4, 4, 0, 0]} />
            </BarChart>
          </ResponsiveContainer>
        )}
      </div>
      {/* Table: Top 10 Products by Revenue */}
      <div>
        <h2 className="text-lg font-semibold mb-4" style={{ color: "#231f20" }}>Top Products by Revenue</h2>
        {detailLoading ? (
          <div className="space-y-3">
            {[...Array(5)].map((_, i) => (
              <div key={i} className="h-8 bg-gray-200 animate-pulse rounded" />
            ))}
          </div>
        ) : (
          <div className="overflow-x-auto">
            <table className="w-full text-sm">
              <thead>
                <tr className="border-b border-gray-200">
                  <th className="text-left py-3 font-semibold" style={{ color: "#231f20" }}>Product</th>
                  <th className="text-left py-3 font-semibold" style={{ color: "#231f20" }}>Category</th>
                  <th className="text-right py-3 font-semibold" style={{ color: "#231f20" }}>Revenue</th>
                  <th className="text-right py-3 font-semibold" style={{ color: "#231f20" }}>Orders</th>
                </tr>
              </thead>
              <tbody>
                {detailRows.map((row, i) => (
                  <tr key={i} className="border-b border-gray-200"
                      style={{ backgroundColor: i % 2 === 0 ? "transparent" : "#f0f0f0" }}>
                    <td className="py-3" style={{ color: "#231f20" }}>{row.product_name}</td>
                    <td className="py-3" style={{ color: "#6a6a6a" }}>{row.category}</td>
                    <td className="text-right py-3" style={{ color: "#231f20" }}>
                      ${N(row.revenue).toLocaleString()}
                    </td>
                    <td className="text-right py-3" style={{ color: "#6a6a6a" }}>
                      {N(row.orders).toLocaleString()}
                    </td>
                  </tr>
                ))}
              </tbody>
            </table>
          </div>
        )}
      </div>
    </div>
  );
}

2. Product Analytics Dashboard

KPIs: Active Users, Sessions, Avg Session Duration, Conversion Rate. Charts: Daily Active Users (Area), Feature Usage (Bar). Table: Top Pages.

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

const N = (v: unknown): number => (v != null ? Number(v) : 0);
const COLORS = ["#0777b3", "#bd4e35", "#2d7a00", "#e18727", "#638CAD", "#adadad"];

export default function ProductAnalyticsDashboard() {
  const { data: kpiData, isLoading: kpiLoading, isError: kpiError, error: kpiMsg } = useSQLQuery(`
    SELECT COUNT(DISTINCT user_id) AS active_users, COUNT(DISTINCT session_id) AS total_sessions,
           ROUND(AVG(session_duration_sec) / 60.0, 1) AS avg_session_min,
           ROUND(100.0 * SUM(CASE WHEN converted = true THEN 1 ELSE 0 END) / COUNT(*), 1) AS conversion_rate
    FROM "product_db"."main"."sessions"
    WHERE session_start >= CURRENT_DATE - INTERVAL 30 DAY
  `);
  const kpiRows = Array.isArray(kpiData) ? kpiData : [];
  const { data: dauData, isLoading: dauLoading } = useSQLQuery(`
    SELECT strftime(date_trunc('day', session_start), '%Y-%m-%d') AS day,
           COUNT(DISTINCT user_id) AS active_users
    FROM "product_db"."main"."sessions"
    WHERE session_start >= CURRENT_DATE - INTERVAL 30 DAY
    GROUP BY 1 ORDER BY 1
  `);
  const dauRows = Array.isArray(dauData) ? dauData : [];
  const { data: featureData, isLoading: featureLoading } = useSQLQuery(`
    SELECT feature_name, COUNT(*) AS usage_count
    FROM "product_db"."main"."feature_events"
    WHERE event_time >= CURRENT_DATE - INTERVAL 30 DAY
    GROUP BY 1 ORDER BY 2 DESC LIMIT 8
  `);
  const featureRows = Array.isArray(featureData) ? featureData : [];
  const { data: pageData, isLoading: pageLoading } = useSQLQuery(`
    SELECT page_path, COUNT(*) AS views, COUNT(DISTINCT user_id) AS unique_visitors
    FROM "product_db"."main"."page_views"
    WHERE view_time >= CURRENT_DATE - INTERVAL 30 DAY
    GROUP BY 1 ORDER BY 2 DESC LIMIT 10
  `);
  const pageRows = Array.isArray(pageData) ? pageData : [];
  const KPI = ({ label, value, suffix = "" }: {
    label: string; value: string; suffix?: string;
  }) => (
    <div>
      <p className="text-sm mb-1" style={{ color: "#6a6a6a" }}>{label}</p>
      {kpiLoading ? (
        <div className="h-12 w-24 bg-gray-200 animate-pulse rounded" />
      ) : (
        <p className="text-5xl font-bold" style={{ color: "#231f20" }}>{value}{suffix}</p>
      )}
    </div>
  );

  return (
    <div className="p-8 min-h-screen" style={{ backgroundColor: "#f8f8f8" }}>
      <h1 className="text-2xl font-bold mb-8" style={{ color: "#231f20" }}>Product Analytics</h1>
      {/* KPIs */}
      <div className="grid grid-cols-4 gap-8 mb-10">
        <KPI label="Active Users (30d)" value={N(kpiRows[0]?.active_users).toLocaleString()} />
        <KPI label="Sessions" value={N(kpiRows[0]?.total_sessions).toLocaleString()} />
        <KPI label="Avg Session Duration" value={N(kpiRows[0]?.avg_session_min).toFixed(1)} suffix=" min" />
        <KPI label="Conversion Rate" value={N(kpiRows[0]?.conversion_rate).toFixed(1)} suffix="%" />
      </div>
      {kpiError && (
        <p className="text-sm mb-4" style={{ color: "#bd4e35" }}>
          Failed to load KPIs: {kpiMsg?.message || "Unknown error"}
        </p>
      )}
      {/* Chart 1: Daily Active Users Trend */}
      <div className="mb-10">
        <h2 className="text-lg font-semibold mb-4" style={{ color: "#231f20" }}>Daily Active Users</h2>
        {dauLoading ? (
          <div className="flex items-center justify-center h-64">
            <Loader2 className="animate-spin" size={32} style={{ color: "#0777b3" }} />
          </div>
        ) : (
          <ResponsiveContainer width="100%" height={260}>
            <AreaChart data={dauRows}>
              <CartesianGrid strokeDasharray="3 3" stroke="#e0e0e0" />
              <XAxis dataKey="day" tick={{ fontSize: 12 }} />
              <YAxis tick={{ fontSize: 12 }} />
              <Tooltip />
              <Area
                type="monotone"
                dataKey="active_users"
                stroke={COLORS[0]}
                fill={COLORS[0]}
                fillOpacity={0.15}
              />
            </AreaChart>
          </ResponsiveContainer>
        )}
      </div>
      {/* Chart 2: Feature Usage Breakdown */}
      <div className="mb-10">
        <h2 className="text-lg font-semibold mb-4" style={{ color: "#231f20" }}>Feature Usage</h2>
        {featureLoading ? (
          <div className="flex items-center justify-center h-64">
            <Loader2 className="animate-spin" size={32} style={{ color: "#0777b3" }} />
          </div>
        ) : (
          <ResponsiveContainer width="100%" height={260}>
            <BarChart data={featureRows}>
              <CartesianGrid strokeDasharray="3 3" stroke="#e0e0e0" />
              <XAxis dataKey="feature_name" tick={{ fontSize: 12 }} />
              <YAxis tick={{ fontSize: 12 }} />
              <Tooltip />
              <Bar dataKey="usage_count" fill={COLORS[0]} radius={[4, 4, 0, 0]} />
            </BarChart>
          </ResponsiveContainer>
        )}
      </div>
      {/* Table: Top Pages by Views */}
      <div>
        <h2 className="text-lg font-semibold mb-4" style={{ color: "#231f20" }}>Top Pages</h2>
        {pageLoading ? (
          <div className="space-y-3">
            {[...Array(5)].map((_, i) => (
              <div key={i} className="h-8 bg-gray-200 animate-pulse rounded" />
            ))}
          </div>
        ) : (
          <div className="overflow-x-auto">
            <table className="w-full text-sm">
              <thead>
                <tr className="border-b border-gray-200">
                  <th className="text-left py-3 font-semibold" style={{ color: "#231f20" }}>Page</th>
                  <th className="text-right py-3 font-semibold" style={{ color: "#231f20" }}>Views</th>
                  <th className="text-right py-3 font-semibold" style={{ color: "#231f20" }}>Unique Visitors</th>
                </tr>
              </thead>
              <tbody>
                {pageRows.map((row, i) => (
                  <tr key={i} className="border-b border-gray-200"
                      style={{ backgroundColor: i % 2 === 0 ? "transparent" : "#f0f0f0" }}>
                    <td className="py-3" style={{ color: "#231f20" }}>{row.page_path}</td>
                    <td className="text-right py-3" style={{ color: "#231f20" }}>
                      {N(row.views).toLocaleString()}
                    </td>
                    <td className="text-right py-3" style={{ color: "#6a6a6a" }}>
                      {N(row.unique_visitors).toLocaleString()}
                    </td>
                  </tr>
                ))}
              </tbody>
            </table>
          </div>
        )}
      </div>
    </div>
  );
}

3. Operational Metrics Dashboard

KPIs: Total Requests, Error Rate, P95 Latency, Uptime. Charts: Request Volume (Area), Error Rate by Endpoint (Bar). Table: Slowest Endpoints.

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

const N = (v: unknown): number => (v != null ? Number(v) : 0);
const COLORS = ["#0777b3", "#bd4e35", "#2d7a00", "#e18727", "#638CAD", "#adadad"];

export default function OperationalMetricsDashboard() {
  const { data: kpiData, isLoading: kpiLoading, isError: kpiError, error: kpiMsg } = useSQLQuery(`
    SELECT COUNT(*) AS total_requests,
           ROUND(100.0 * SUM(CASE WHEN status_code >= 500 THEN 1 ELSE 0 END) / COUNT(*), 2) AS error_rate,
           ROUND(quantile_cont(latency_ms, 0.95), 0) AS p95_latency_ms,
           ROUND(100.0 * SUM(CASE WHEN status_code < 500 THEN 1 ELSE 0 END) / COUNT(*), 2) AS uptime_pct
    FROM "ops_db"."main"."requests" WHERE request_time >= CURRENT_DATE - INTERVAL 24 HOUR
  `);
  const kpiRows = Array.isArray(kpiData) ? kpiData : [];
  const { data: volumeData, isLoading: volumeLoading } = useSQLQuery(`
    SELECT strftime(date_trunc('hour', request_time), '%Y-%m-%d %H:00') AS hour, COUNT(*) AS requests
    FROM "ops_db"."main"."requests"
    WHERE request_time >= CURRENT_DATE - INTERVAL 24 HOUR
    GROUP BY 1 ORDER BY 1
  `);
  const volumeRows = Array.isArray(volumeData) ? volumeData : [];
  const { data: errorData, isLoading: errorLoading } = useSQLQuery(`
    SELECT endpoint,
           ROUND(100.0 * SUM(CASE WHEN status_code >= 500 THEN 1 ELSE 0 END) / COUNT(*), 2) AS error_rate
    FROM "ops_db"."main"."requests"
    WHERE request_time >= CURRENT_DATE - INTERVAL 24 HOUR
    GROUP BY 1 HAVING COUNT(*) >= 10 ORDER BY 2 DESC LIMIT 8
  `);
  const errorRows = Array.isArray(errorData) ? errorData : [];
  const { data: slowData, isLoading: slowLoading } = useSQLQuery(`
    SELECT endpoint, COUNT(*) AS requests, ROUND(AVG(latency_ms), 0) AS avg_latency_ms,
           ROUND(quantile_cont(latency_ms, 0.95), 0) AS p95_latency_ms,
           ROUND(quantile_cont(latency_ms, 0.99), 0) AS p99_latency_ms
    FROM "ops_db"."main"."requests"
    WHERE request_time >= CURRENT_DATE - INTERVAL 24 HOUR
    GROUP BY 1 HAVING COUNT(*) >= 10 ORDER BY 4 DESC LIMIT 10
  `);
  const slowRows = Array.isArray(slowData) ? slowData : [];
  const KPI = ({ label, value, suffix = "" }: {
    label: string; value: string; suffix?: string;
  }) => (
    <div>
      <p className="text-sm mb-1" style={{ color: "#6a6a6a" }}>{label}</p>
      {kpiLoading ? (
        <div className="h-12 w-24 bg-gray-200 animate-pulse rounded" />
      ) : (
        <p className="text-5xl font-bold" style={{ color: "#231f20" }}>{value}{suffix}</p>
      )}
    </div>
  );

  const errorRateColor = (rate: number): string => {
    if (rate >= 5) return "#bd4e35";
    if (rate >= 1) return "#e18727";
    return "#2d7a00";
  };

  return (
    <div className="p-8 min-h-screen" style={{ backgroundColor: "#f8f8f8" }}>
      <h1 className="text-2xl font-bold mb-8" style={{ color: "#231f20" }}>Operational Metrics</h1>
      {/* KPIs */}
      <div className="grid grid-cols-4 gap-8 mb-10">
        <KPI label="Total Requests (24h)" value={N(kpiRows[0]?.total_requests).toLocaleString()} />
        <div>
          <p className="text-sm mb-1" style={{ color: "#6a6a6a" }}>Error Rate</p>
          {kpiLoading ? (
            <div className="h-12 w-24 bg-gray-200 animate-pulse rounded" />
          ) : (
            <p className="text-5xl font-bold"
               style={{ color: errorRateColor(N(kpiRows[0]?.error_rate)) }}>
              {N(kpiRows[0]?.error_rate).toFixed(2)}%
            </p>
          )}
        </div>
        <KPI label="P95 Latency" value={N(kpiRows[0]?.p95_latency_ms).toLocaleString()} suffix=" ms" />
        <KPI label="Uptime" value={N(kpiRows[0]?.uptime_pct).toFixed(2)} suffix="%" />
      </div>
      {kpiError && (
        <p className="text-sm mb-4" style={{ color: "#bd4e35" }}>
          Failed to load KPIs: {kpiMsg?.message || "Unknown error"}
        </p>
      )}
      {/* Chart 1: Request Volume Over Time */}
      <div className="mb-10">
        <h2 className="text-lg font-semibold mb-4" style={{ color: "#231f20" }}>Request Volume (Hourly)</h2>
        {volumeLoading ? (
          <div className="flex items-center justify-center h-64">
            <Loader2 className="animate-spin" size={32} style={{ color: "#0777b3" }} />
          </div>
        ) : (
          <ResponsiveContainer width="100%" height={260}>
            <AreaChart data={volumeRows}>
              <CartesianGrid strokeDasharray="3 3" stroke="#e0e0e0" />
              <XAxis dataKey="hour" tick={{ fontSize: 12 }} />
              <YAxis tick={{ fontSize: 12 }} />
              <Tooltip />
              <Area
                type="monotone"
                dataKey="requests"
                stroke={COLORS[0]}
                fill={COLORS[0]}
                fillOpacity={0.15}
              />
            </AreaChart>
          </ResponsiveContainer>
        )}
      </div>
      {/* Chart 2: Error Rate by Endpoint */}
      <div className="mb-10">
        <h2 className="text-lg font-semibold mb-4" style={{ color: "#231f20" }}>Error Rate by Endpoint</h2>
        {errorLoading ? (
          <div className="flex items-center justify-center h-64">
            <Loader2 className="animate-spin" size={32} style={{ color: "#0777b3" }} />
          </div>
        ) : (
          <ResponsiveContainer width="100%" height={260}>
            <BarChart data={errorRows}>
              <CartesianGrid strokeDasharray="3 3" stroke="#e0e0e0" />
              <XAxis dataKey="endpoint" tick={{ fontSize: 12 }} />
              <YAxis tick={{ fontSize: 12 }} tickFormatter={(v) => `${v}%`} />
              <Tooltip formatter={(value: number) => `${value}%`} />
              <Bar dataKey="error_rate" fill={COLORS[1]} radius={[4, 4, 0, 0]} />
            </BarChart>
          </ResponsiveContainer>
        )}
      </div>
      {/* Table: Slowest Endpoints */}
      <div>
        <h2 className="text-lg font-semibold mb-4" style={{ color: "#231f20" }}>Slowest Endpoints</h2>
        {slowLoading ? (
          <div className="space-y-3">
            {[...Array(5)].map((_, i) => (
              <div key={i} className="h-8 bg-gray-200 animate-pulse rounded" />
            ))}
          </div>
        ) : (
          <div className="overflow-x-auto">
            <table className="w-full text-sm">
              <thead>
                <tr className="border-b border-gray-200">
                  <th className="text-left py-3 font-semibold" style={{ color: "#231f20" }}>Endpoint</th>
                  <th className="text-right py-3 font-semibold" style={{ color: "#231f20" }}>Requests</th>
                  <th className="text-right py-3 font-semibold" style={{ color: "#231f20" }}>Avg Latency</th>
                  <th className="text-right py-3 font-semibold" style={{ color: "#231f20" }}>P95 Latency</th>
                  <th className="text-right py-3 font-semibold" style={{ color: "#231f20" }}>P99 Latency</th>
                </tr>
              </thead>
              <tbody>
                {slowRows.map((row, i) => (
                  <tr key={i} className="border-b border-gray-200"
                      style={{ backgroundColor: i % 2 === 0 ? "transparent" : "#f0f0f0" }}>
                    <td className="py-3" style={{ color: "#231f20" }}>{row.endpoint}</td>
                    <td className="text-right py-3" style={{ color: "#6a6a6a" }}>
                      {N(row.requests).toLocaleString()}
                    </td>
                    <td className="text-right py-3" style={{ color: "#231f20" }}>
                      {N(row.avg_latency_ms).toLocaleString()} ms
                    </td>
                    <td className="text-right py-3" style={{ color: "#231f20" }}>
                      {N(row.p95_latency_ms).toLocaleString()} ms
                    </td>
                    <td className="text-right py-3"
                        style={{ color: N(row.p99_latency_ms) > 1000 ? "#bd4e35" : "#231f20" }}>
                      {N(row.p99_latency_ms).toLocaleString()} ms
                    </td>
                  </tr>
                ))}
              </tbody>
            </table>
          </div>
        )}
      </div>
    </div>
  );
}

---

Adapting Templates

• Replace table names with your actual fully qualified names ("db"."schema"."table").
• Replace column names to match your schema. Use motherduck-explore to discover columns.
• Adjust aggregations to match your data (e.g., SUM(amount) vs. SUM(quantity * unit_price)).
• Adjust date granularity: change date_trunc('month', ...) to 'day', 'week', 'hour', or 'quarter'.
• Adjust time windows: change INTERVAL 30 DAY to match your reporting period.

All templates share: one useSQLQuery per section, N() and COLORS at file top, Array.isArray guards, per-section loading states, export default function, #f8f8f8 background, no card borders.