Assignment 4-A

Data Analysis Queries

Build powerful analytical queries using aggregate functions, GROUP BY, HAVING clauses, and window functions to extract meaningful insights from sales data.

5-6 hours
Intermediate
125 Points
What You'll Practice
  • COUNT, SUM, AVG, MIN, MAX
  • GROUP BY for data grouping
  • HAVING for group filtering
  • ROW_NUMBER, RANK, DENSE_RANK
  • LAG, LEAD window functions
Contents
01

Assignment Overview

In this assignment, you will analyze a Sales Database to generate business insights. You'll use aggregate functions to summarize data, GROUP BY to organize results, and window functions for advanced analytics like rankings and running totals.

Skills Applied: This assignment tests your understanding of Aggregate Functions (Topic 4.1), GROUP BY and HAVING (Topic 4.2), and Window Functions (Topic 4.3) from Module 4.
Aggregation

COUNT, SUM, AVG, MIN, MAX

Grouping

GROUP BY, HAVING, multiple groups

Window Functions

RANK, ROW_NUMBER, LAG, LEAD

02

The Scenario

Sales Analytics Dashboard

You are building the backend queries for a Sales Analytics Dashboard. The marketing team needs various reports and metrics to track performance.

"We need to know our top products, sales trends by month, regional performance, and how each salesperson ranks compared to others."

03

Query Requirements

1
Basic Aggregation (5 queries)
  • Total revenue and order count
  • Average order value
  • Minimum and maximum sale amounts
  • Count of distinct customers
  • Sum with NULL handling
2
GROUP BY Queries (5 queries)
  • Sales by product category
  • Monthly revenue trends
  • Revenue by region and salesperson
  • Top 5 customers by total spending
  • Products with sales above average (using HAVING)
3
Window Function Queries (5 queries)
  • Rank products by revenue within each category
  • Calculate running total of sales by date
  • Compare each sale to previous sale (LAG)
  • Identify top 3 products per category (ROW_NUMBER)
  • Calculate month-over-month growth percentage
04

Submission Guidelines

GitHub Repository
  • Create a repository named sql-assignment-4-analytics
  • Include queries.sql with all 15 analytical queries
  • Each query should include expected output description
  • Add sample result screenshots for verification
05

Grading Rubric

Criteria Points
Basic aggregation queries (5 × 7 pts) 35
GROUP BY queries (5 × 8 pts) 40
Window function queries (5 × 10 pts) 50
Total 125