SQL Exercise – 3

SQL Exercise:

Write a query to find the top 3 cities by total sales revenue, including:

1. The total revenue for each city.
2. The number of unique customers who placed orders in that city.
3. Exclude cities with total sales revenue below $1,000.

Table Creation Scripts:

1. Create Products Table

CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(50),
Category VARCHAR(50),
Price DECIMAL(10, 2)
);

CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
Name VARCHAR(50),
City VARCHAR(50)
);

CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
ProductID INT,
Quantity INT,
OrderDate DATE
);

Data Insertion Scripts:

INSERT INTO Products (ProductID, ProductName, Category, Price)
VALUES
(101, 'Laptop', 'Electronics', 1000),
(102, 'Smartphone', 'Electronics', 800),
(103, 'Headphones', 'Accessories', 150),
(104, 'Desk Chair', 'Furniture', 200),
(105, 'Monitor', 'Electronics', 300);

INSERT INTO Customers (CustomerID, Name, City)
VALUES
(1, 'Alice', 'New York'),
(2, 'Bob', 'San Francisco'),
(3, 'Charlie', 'Chicago'),
(4, 'David', 'New York'),
(5, 'Eve', 'Chicago');

INSERT INTO Orders (OrderID, CustomerID, ProductID, Quantity, OrderDate)
VALUES
(1001, 1, 101, 1, '2024-01-10'),
(1002, 2, 102, 2, '2024-01-15'),
(1003, 3, 104, 1, '2024-01-20'),
(1004, 4, 103, 4, '2024-01-25'),
(1005, 5, 105, 1, '2024-01-30'),
(1006, 2, 101, 1, '2024-02-05');

Solution:

WITH CitySalesRevenue
AS (SELECT CUST.city,
Count(DISTINCT CUST.customerid) AS UniqueCustomers,
Sum(P.price) AS TotalRevenue
FROM orders AS O
INNER JOIN customers AS CUST
ON CUST.customerid = O.customerid
INNER JOIN products P
ON O.productid = P.productid
GROUP BY CUST.city
HAVING Sum(P.price) > 1000)
SELECT TOP 3 *
FROM CitySalesRevenue
ORDER BY TotalRevenue DESC

Output:

Explanation:

1. CitySalesRevenue CTE:
   • The CTE calculates the total revenue and the number of unique customers for each city.
   • This is equivalent to the main part of the original query but is encapsulated for reusability and clarity.
2. Main Query:
   • The main query selects results from the CityRevenue CTE.
   • It applies a filter (WHERE TotalRevenue >= 1000), sorts the cities by TotalRevenue in descending order, and apply TOP to get the top 3 cities.