This video demonstrates how to calculate monthly sales trends using SQL in an e-commerce project. It covers joining order items and orders tables, filtering delivered orders, extracting year and month from order dates, and grouping results to show revenue per month.
A short editorial from the VEONIB team on why this content matters.
SEONIB highlights this tutorial as a practical SQL exercise for e-commerce analytics, focusing on monthly revenue trends.
The video’s step-by-step approach demystifies JOINs and date functions, making it ideal for beginners. SEONIB adds that such queries are foundational for business intelligence dashboards.
Data analysts and SQL learners should watch this and then practice by modifying the query to include product categories or customer segments.
A report showing total sales or revenue for each month, often broken down by year.
A clause to combine rows from two or more tables based on a related column.
A MySQL function to format a date as a string, e.g., '%Y-%m' for year-month.
A SQL clause to group rows that have the same values in specified columns, often used with aggregate functions.
Computing total income, typically by summing quantity times unit price.
Using WHERE clause to include only orders with a specific status, like 'delivered'.
What is a monthly sales trend?
It is a report showing total sales or revenue for each month and year, helping to identify patterns over time.
Why do we need to join order_items and orders tables?
To get both product details (quantity, price) and order dates, which are in separate tables.
How to extract year and month from a date in SQL?
Use DATE_FORMAT(order_date, '%Y-%m') to get a string like '2025-01'.
What does GROUP BY do in this context?
It groups rows by the extracted year-month, allowing SUM to calculate revenue per month.
How to calculate revenue in SQL?
Use SUM(quantity * unit_price) from the order_items table.
Why filter with 'WHERE order_status = delivered'?
To consider only actual sales that were delivered, excluding cancelled or pending orders.
How to sort months chronologically?
Add ORDER BY on the formatted date column (e.g., trend) to sort by year and month.
Can I use the alias in GROUP BY?
It may work in some databases like MySQL, but it's safer to use the full expression for portability.
What is the SQL execution order?
FROM, JOIN, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.
Why beautify SQL code?
To improve readability and make debugging easier, especially for complex queries.