Prepare Dashboard Metrics with SQL

Context

Dashboard queries are common in analytics and data engineering interviews because they test whether you can turn raw data into reliable business metrics. Interviewers want to see that you understand both query logic and data quality considerations.

Question

Explain how you would use SQL to prepare data for a dashboard. In your answer, describe how you would:

1. Filter and clean raw records
2. Aggregate data to the right reporting grain
3. Handle missing or duplicate values
4. Structure output for common dashboard metrics such as daily totals, counts, or averages

Scope Guidance

Keep the discussion practical and SQL-focused. You do not need to design a full BI system, but you should explain the main steps, common SQL patterns, and how you would make the output reliable and easy for a dashboard to consume.

Choosing the reporting grain

Before writing SQL, define the level of detail the dashboard needs, such as daily, weekly, or monthly metrics by product, region, or customer segment. The grain determines the GROUP BY columns and prevents mixing incompatible levels of aggregation.

SELECT order_date, SUM(revenue) AS daily_revenue
FROM orders
GROUP BY order_date;

Data cleaning and filtering

Dashboard queries usually exclude invalid or incomplete records, such as canceled orders, test data, or NULL values in required fields. Cleaning logic should be explicit in the WHERE clause so the metric definition is consistent and auditable.

SELECT order_date, SUM(revenue) AS daily_revenue
FROM orders
WHERE status = 'completed'
  AND revenue IS NOT NULL
GROUP BY order_date;

Aggregation for business metrics

Dashboards typically need aggregated measures like counts, sums, averages, minimums, or maximums. SQL aggregations convert row-level events into compact metric tables that visualization tools can query efficiently.

SELECT order_date, COUNT(*) AS order_count, AVG(revenue) AS avg_order_value
FROM orders
WHERE status = 'completed'
GROUP BY order_date;

Handling NULLs and duplicates

Missing values and duplicate records can distort dashboard metrics if they are not handled carefully. SQL uses functions like COALESCE and patterns like DISTINCT or grouped deduplication to make outputs more reliable.

SELECT customer_id, COALESCE(city, 'Unknown') AS city
FROM customers;

Producing dashboard-friendly output

A good dashboard query returns clearly named columns, stable metric definitions, and a predictable sort order. This makes the result easier to plug into BI tools and reduces ambiguity for downstream users.

SELECT order_date, SUM(revenue) AS daily_revenue
FROM orders
WHERE status = 'completed'
GROUP BY order_date
ORDER BY order_date;