Market Analysis using SQL

Market Analysis is a popular SQL question in Data Science interviews. It is often asked to understand your ability to manipulate and analyze data using SQL operations such as filtering, aggregating, sorting, and joining tables. So, if you want to know how to perform market analysis using SQL, this article is for you. In this article, I will take you through the task of Market Analysis using SQL.

Market Analysis: Problem Statement

A company has provided sales data containing information about customer purchases, as shown in the table below.

+------------+-----------+--------------+----------+---------+
| CustomerID | ProductID | PurchaseDate | Quantity | Revenue |
+------------+-----------+--------------+----------+---------+
|          1 | A         | 2023-01-01   |        5 |  100.00 |
|          2 | B         | 2023-01-02   |        3 |   50.00 |
|          3 | A         | 2023-01-03   |        2 |   30.00 |
|          4 | C         | 2023-01-03   |        1 |   20.00 |
|          1 | B         | 2023-01-04   |        4 |   80.00 |
+------------+-----------+--------------+----------+---------+

Your task is to:

  1. Calculate Total Revenue
  2. Calculate Total Sales by Product
  3. Find Top Customers by Revenue

Market Analysis using SQL

So let’s solve all these problems one by one for the task of Market Analysis using SQL. First, let’s create the input table for our SQL database (I am using MySql):

CREATE TABLE Sales (
    CustomerID INT,
    ProductID CHAR(1),
    PurchaseDate DATE,
    Quantity INT,
    Revenue DECIMAL(10, 2)
);

INSERT INTO Sales (CustomerID, ProductID, PurchaseDate, Quantity, Revenue)
VALUES
    (1, 'A', '2023-01-01', 5, 100),
    (2, 'B', '2023-01-02', 3, 50),
    (3, 'A', '2023-01-03', 2, 30),
    (4, 'C', '2023-01-03', 1, 20),
    (1, 'B', '2023-01-04', 4, 80);

Calculating total revenue:

SELECT SUM(Revenue) AS TotalRevenue FROM Sales;
+--------------+
| TotalRevenue |
+--------------+
|       280.00 |
+--------------+
1 row in set (0.01 sec)

This query calculates the total revenue earned from all the sales recorded in the “Sales” table. It does so by adding the values in the “Revenue” column. The result of this calculation is given the name “TotalRevenue” for better identification and reference.

Calculating total sales by product:

SELECT ProductID, SUM(Quantity) AS TotalQuantity, SUM(Revenue) AS TotalRevenue
FROM Sales
GROUP BY ProductID;
+-----------+---------------+--------------+
| ProductID | TotalQuantity | TotalRevenue |
+-----------+---------------+--------------+
| A         |             7 |       130.00 |
| B         |             7 |       130.00 |
| C         |             1 |        20.00 |
+-----------+---------------+--------------+
3 rows in set (0.00 sec)

This query retrieves information from the “Sales” table and calculates the total quantity sold and revenue generated for each product. By grouping the data based on the unique product IDs, the query calculates the sum of the quantities and revenues associated with each product. The results are presented with the respective product IDs, total quantities, and total revenues for further analysis or reporting.

Finding top customers by revenue:

SELECT CustomerID, SUM(Revenue) AS TotalRevenue
FROM Sales
GROUP BY CustomerID
ORDER BY TotalRevenue DESC
LIMIT 5;
+------------+--------------+
| CustomerID | TotalRevenue |
+------------+--------------+
|          1 |       180.00 |
|          2 |        50.00 |
|          3 |        30.00 |
|          4 |        20.00 |
+------------+--------------+
4 rows in set (0.00 sec)

This query retrieves information from the “Sales” table and calculates the total revenue generated by each customer. By grouping the data based on the unique customer IDs, the query calculates the sum of the revenues associated with each customer. The results are then sorted in descending order based on the total revenue and limited to the top 5 rows, allowing us to identify the customers with the highest total revenues.

Summary

So this is how you can perform Market Analysis using SQL. These were just examples of the kind of problems you will face in Market Analysis. More questions can be asked to test your ability to manipulate and analyze data using SQL operations such as filtering, aggregating, sorting, and joining tables. I hope you liked this article on Market Analysis using SQL. Feel free to ask valuable questions in the comments section below.

Aman Kharwal
Aman Kharwal

I'm a writer and data scientist on a mission to educate others about the incredible power of data📈.

Articles: 1498

One comment

Leave a Reply