Analyze Performance Metrics with Aggregates using SQL in PostgreSQL

Table of Contents

1. Challenge

   Step 1: Explore the Sales Dataset

   Working in this lab

   Use the files under sql/ to write your queries, then click Validate on each task to check your work. ### Explore the Sales Dataset

   This step introduces the core dataset used throughout the lab.

   Before performing aggregations, you must understand the structure of the sales and employee tables and identify which columns represent measurable values versus grouping dimensions. Establishing this foundation ensures that later aggregate queries are both accurate and meaningful. info> This lab experience was developed by the Pluralsight team using Forge, an internally developed AI tool utilizing Gemini technology. All sections were verified by human experts for accuracy prior to publication. For issue reporting, please contact us.

2. Challenge

   Step 2: Calculate Basic Aggregate Metrics

   This step introduces aggregate functions, which allows you to summarize large volumes of transactional data into meaningful metrics.

   You will calculate:

   • Total sales using SUM()
   • Average transaction value using AVG()
   • Transaction counts using COUNT()

   These metrics are foundational in analytics and reporting. They transform raw rows into insights that help answer questions like:

   • How much revenue did you generate?
   • What is the typical sale size?
   • How many transactions occurred?

   Understanding these basics is essential before moving into grouped and segmented analysis.

3. Challenge

   Step 3: Group Data by Region and Department

   In this step, you’ll move from overall metrics to segmented analysis using GROUP BY.

   Instead of calculating a single total, you’ll break results down by business dimensions such as:

   • region
   • department

   This allows you to answer more targeted questions, such as:

   • Which regions generate the most revenue?
   • Which departments perform best?

   Grouping is a core SQL skill because it enables comparative analysis across different parts of the business.

   In this lab, you may see table aliases such as s (for sales) and e (for employees). These are shorthand references to tables.

   Column aliases (for example, total_sales) are different—they rename the output column and must match the names specified in each task.

4. Challenge

   Step 4: Filter Aggregated Results with HAVING

   This step introduces the HAVING clause, which allows you to filter aggregated results.

   While WHERE filters rows before aggregation, HAVING filters results after aggregation. This distinction is critical when working with grouped data.

   You will use HAVING to:

   • Identify high-performing regions
   • Filter employees with significant activity
   • Refine grouped insights into actionable results

   This step helps you move from raw summaries to focused, decision-ready outputs.

5. Challenge

   Step 5: Rank Performance with Window Functions

   In this step, you will work with window functions, which allow you to perform calculations across sets of rows without collapsing them like GROUP BY does.

   You’ll use:

   • ROW_NUMBER() to assign a unique order within groups
   • RANK() to identify top performers
   • PARTITION BY to segment rankings by region

   These functions are powerful for answering questions like:

   • Who is the top performer in each region?
   • How do employees rank within their group?

   Window functions are widely used in real-world analytics, especially for leaderboards, rankings, and comparative performance analysis.

6. Challenge

   Step 6: Create Calculated Metrics for Insights

   In this final step, you will create calculated metrics that provide deeper analytical insight.

   Instead of just reporting totals, you will calculate:

   • Percentage of total sales by region
   • Contribution of each employee
   • Formatted outputs for readability

   These types of calculations are commonly used in dashboards and executive reports to show relative performance, not just raw values.

   By the end of this step, you will be able to transform aggregated data into polished, insight-driven results suitable for decision-making.