Sales report (SQL querying)

See the Sales report preparation note to create your database for this example

erDiagram
    Customers ||--o{ Sales : has
    Products ||--o{ Sales : has

    Customers {
        int customer_id
        string customer_name
        string region
    }
    
    Products {
        int product_id
        string product_name
        string category
    }
    
    Sales {
        int sales_id
        int customer_id
        int product_id
        int quantity
        decimal price
        date sale_date
        string channel
   }

Queries

1 - What is the total sales revenue for each year?

SELECT YEAR(sale_date) AS sale_year, SUM(price) AS total_sales_revenue
FROM Sales
GROUP BY YEAR(sale_date);

2 - What is the average sales revenue per month for the year 2022?

SELECT MONTH(sale_date) AS sales_month, AVG(price) AS average_sales_revenue
FROM Sales
WHERE YEAR(sale_date) = 2023
GROUP BY MONTH(sale_date);

3 - Which product category has the highest sales revenue in the year 2023?

SELECT p.category, SUM(s.price) AS total_sales_revenue
FROM Sales s
JOIN Products p ON s.product_id = p.product_id
WHERE YEAR(s.sale_date) = 2023
GROUP BY p.category
ORDER BY total_sales_revenue DESC
LIMIT 10;

4 - Who are the top 5 customers based on total sales revenue?

SELECT c.customer_name, SUM(s.price) AS total_sales_revenue
FROM Sales s
JOIN Customers c ON s.customer_id = c.customer_id
GROUP BY c.customer_name
ORDER BY total_sales_revenue DESC
LIMIT 5;

5 - Calculate the total sales amount and the number of sales per month?

SELECT 
  DATE_FORMAT(sale_date, '%m') AS month,
  SUM(price) AS total_sales_amount,
  COUNT(*) AS total_sales
FROM 
  Sales
GROUP BY 
  month
ORDER BY 
  month ASC;

6 - Get the top 5 customers who made the highest total sales?

SELECT 
  customer_id, 
  SUM(price) AS total_sales_amount
FROM 
  Sales
GROUP BY 
  customer_id
ORDER BY 
  total_sales_amount DESC
LIMIT 5;

7 - Get the total sales amount and the number of sales for each category?

SELECT 
  Products.category,
  SUM(price) AS total_sales_amount,
  COUNT(*) AS total_sales
FROM 
  Sales
JOIN 
  Products ON Sales.product_id = Products.product_id
GROUP BY 
  Products.category;

8 - Get the total sales amount and the number of sales for each product in January 2022

SELECT 
  Sales.product_id,
  Products.product_name,
  SUM(price) AS total_sales_amount,
  COUNT(*) AS total_sales
FROM 
  Sales
JOIN 
  Products ON Sales.product_id = Products.product_id
WHERE 
  sale_date BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY 
  Sales.product_id,
  Products.product_name;

10 - Get the total sales amount and the number of sales for each channel?

SELECT 
  channel,
  DATE_FORMAT(sale_date, '%Y-%m') AS month,
  SUM(price) AS total_sales_amount,
  COUNT(*) AS total_sales
FROM 
  Sales
GROUP BY 
  channel, month
ORDER BY month ASC;

Experiment Queries

//1: Total sales revenue for each year
//2: Average sales revenue per month for the year 2022?
//3: Product category has the highest sales revenue in the year 2023
//4: Top 5 customers based on total sales revenue
//5: Total sales amount and the number of sales per month
//6: Top 5 customers who made the highest total sales
//7: Total sales amount and the number of sales for each category
//8: Total sales amount and the number of sales for each product in January 2022
//9: Total sales amount and the number of sales for each channel
const sql = loadBlock("1");
const json = await localSalesSQL(sql);
printJSON(json)

With graph

// Total sales amount and the number of sales for each channel
const sql = loadBlock("9");
const json = await localSalesSQL(sql);

const series = toSeries({data:json, 
  x:"month", 
  y:"total_sales_amount", 
  category:"channel" // not mandatory
});

barChart({
  series: series
});