Building a Dynamic Financial Dashboard Using SQL Window Functions

Financial dashboards are essential for tracking business performance over time, providing quick insights into sales, revenue, expenses, and other key financial metrics. SQL window functions are powerful tools that allow you to perform complex calculations across rows while maintaining individual row context. In this tutorial, you'll learn how to build a simple and dynamic financial dashboard by leveraging SQL window functions like ROW_NUMBER(), RANK(), SUM(), and AVG().

Let's assume you have a table named `financials` with the following structure:

CREATE TABLE financials (
  transaction_id INT,
  transaction_date DATE,
  department VARCHAR(50),
  amount DECIMAL(10, 2)
);

This table stores financial transactions by date, department, and amount. To build a dynamic dashboard, we want to calculate metrics such as cumulative revenue, monthly averages, and rank departments by total revenue.

First, let's calculate the cumulative revenue over time, which gives a running total of all transactions ordered by date.

SELECT
  transaction_date,
  SUM(amount) AS daily_revenue,
  SUM(amount) OVER (ORDER BY transaction_date) AS cumulative_revenue
FROM financials
ORDER BY transaction_date;

In this query, `SUM(amount) OVER (ORDER BY transaction_date)` calculates the cumulative sum of amounts ordered by the transaction date. This helps visualize how revenue grows day by day.

Next, let's calculate the average monthly revenue. Using `PARTITION BY` allows you to segment data by each month.

SELECT
  DATE_TRUNC('month', transaction_date) AS month,
  SUM(amount) AS total_revenue,
  AVG(SUM(amount)) OVER () AS average_monthly_revenue
FROM financials
GROUP BY month
ORDER BY month;

Here, `DATE_TRUNC('month', transaction_date)` groups data by month, and `AVG(SUM(amount)) OVER ()` computes the average revenue across all months.

Another useful dashboard metric is ranking departments by total revenue. Window functions like `RANK()` can help you identify top-performing departments.

SELECT
  department,
  SUM(amount) AS total_revenue,
  RANK() OVER (ORDER BY SUM(amount) DESC) AS revenue_rank
FROM financials
GROUP BY department
ORDER BY revenue_rank;

The `RANK()` function assigns a rank to each department based on total revenue in descending order, enabling easy identification of top revenue contributors.

Finally, let's combine some of these ideas to create a more comprehensive view, showing department revenue, cumulative revenue within each department, and rank.

SELECT
  department,
  transaction_date,
  amount,
  SUM(amount) OVER (PARTITION BY department ORDER BY transaction_date) AS cumulative_department_revenue,
  RANK() OVER (PARTITION BY department ORDER BY transaction_date) AS transaction_rank
FROM financials
ORDER BY department, transaction_date;

This query uses `PARTITION BY` to reset cumulative sums and ranks for each department individually, giving you detailed insights into how revenue accumulates over time within each department.

In summary, SQL window functions help you create dynamic and insightful financial dashboards by enabling running totals, rankings, moving averages, and more — all while maintaining row-level details. Experiment with these examples and tweak them to your dataset to build your own customized financial dashboard.

Happy querying!