Demo note on how to query your sales database
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
}
SELECT YEAR(sale_date) AS sale_year, SUM(price) AS total_sales_revenue
FROM Sales
GROUP BY YEAR(sale_date);
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);
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;
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;
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;
SELECT
customer_id,
SUM(price) AS total_sales_amount
FROM
Sales
GROUP BY
customer_id
ORDER BY
total_sales_amount DESC
LIMIT 5;
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;
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;
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;
//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
});