Building Dashboards with SQL

The Dashboard Request

The ops team wanted a "Live Dashboard" showing key metrics. They didn't want to wait for a weekly email; they wanted to see stats any time they opened their browser.

The Quest: The Backend of a Dashboard

A dashboard (Looker, Tableau, Metabase, etc.) is just a visualization layer that runs SQL queries against your database. Your job is to write a "Clean" SQL query that the tool can connect to.

The Implementation: The Dashboard-Ready Query

To be dashboard-ready, a query should:

1. Have clear, aliased column names.

2. Return a single, predictable row-count (e.g., 12 rows for 12 months).

3. Be as fast as possible.

-- The backend for a Monthly Revenue Line Chart

SELECT

DATE_TRUNC('month', order_date) AS "Month",

SUM(order_amount) AS "Revenue",

COUNT(*) AS "Order Count"

FROM orders

WHERE order_date > CURRENT_DATE - INTERVAL '12 months'

GROUP BY "Month"

ORDER BY "Month";

The "Oops" Moment

I once wrote a query that returned millions of rows. The dashboard crashed every time it tried to load.

**Pro Tip**: Dashboards are for summaries. If your query returns more than a few hundred rows, شما should probably add more filters or higher-level grouping.

The Victory

The ops manager now has a live dashboard that refreshes every hour. She made better decisions because she had real-time data. I had learned that my SQL wasn't just "Code"—it was the foundation of intelligence systems.

Your Task for Today

Write a query for a "Weekly Summary" that a dashboard could use. Include clear aliases and an `ORDER BY` on the time column.

*Day 28: The Five Most Common GROUP BY Mistakes.*