Skip to content

Sales Analytics Dashboard Queries

Medium ~25 min

What you'll build

A collection of SQL queries that power a sales analytics dashboard: total revenue, top products, sales by category, and best customers.

Goals

  • Use GROUP BY to aggregate sales by product and category
  • Apply HAVING to filter groups by aggregate values
  • Compute revenue, averages, and rankings
  • Combine JOINs with aggregations for rich reports

Prerequisites

  • SELECT and WHERE basics
  • JOIN fundamentals
  • Aggregate function concepts

Steps

  1. 1

    Create the schema

    Create products and sales tables. Products have a category; sales record each purchase with a quantity and price.

    Code
    CREATE TABLE products (
      id        SERIAL PRIMARY KEY,
      name      TEXT NOT NULL,
      category  TEXT NOT NULL
    );
    
    CREATE TABLE sales (
      id          SERIAL PRIMARY KEY,
      product_id  INTEGER REFERENCES products(id),
      quantity    INTEGER NOT NULL,
      price       NUMERIC(10,2) NOT NULL,
      sold_at     TIMESTAMP DEFAULT NOW()
    );

    Expected result

    Two tables are created with a foreign key relationship.

  2. 2

    Insert sample data

    Insert products in different categories and several sales records.

    Code
    INSERT INTO products (name, category) VALUES
      ('Widget', 'Tools'), ('Gadget', 'Tools'),
      ('Book', 'Media'), ('Pen', 'Office');
    
    INSERT INTO sales (product_id, quantity, price) VALUES
      (1, 5, 9.99), (1, 3, 9.99), (2, 2, 19.99),
      (3, 10, 12.50), (4, 8, 1.99), (4, 4, 1.99);

    Expected result

    Four products and six sales records are inserted.

  3. 3

    Revenue by category

    Write a query that computes total revenue per product category using GROUP BY and SUM.

    Code
    SELECT p.category,
           COUNT(*) AS num_sales,
           SUM(s.quantity * s.price) AS revenue
    FROM sales s
    INNER JOIN products p ON s.product_id = p.id
    GROUP BY p.category
    ORDER BY revenue DESC;

    Expected result

    A summary showing each category's sales count and total revenue.

  4. 4

    Top products with HAVING

    Find products that have sold more than 5 total units, using HAVING to filter after aggregation.

    Code
    SELECT p.name,
           SUM(s.quantity) AS total_units,
           SUM(s.quantity * s.price) AS revenue
    FROM sales s
    INNER JOIN products p ON s.product_id = p.id
    GROUP BY p.name
    HAVING SUM(s.quantity) > 5
    ORDER BY revenue DESC;

    Expected result

    Only products with more than 5 units sold are returned.

  5. 5

    Add indexes to optimize queries

    Create indexes on frequently queried columns to improve query performance. Indexes are the primary means of database performance optimization, but be mindful of write overhead.

    Code
    -- Create indexes on frequently filtered columns
    CREATE INDEX idx_users_email ON users(email);
    CREATE INDEX idx_orders_user_id ON orders(user_id);
    CREATE INDEX idx_orders_created_at ON orders(created_at DESC);
    
    -- Verify the index is used
    EXPLAIN SELECT * FROM orders WHERE user_id = 42;

    Expected result

    EXPLAIN output shows an index scan using idx_orders_user_id instead of a full table scan.

Try it yourself

What you learned

You built real analytics queries combining JOINs, GROUP BY, HAVING, and multiple aggregate functions — the same patterns used in business intelligence dashboards.

Related Resources

Related Lessons

Related Glossary

Related Glossary

A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors

← Back to topic