In the realm of data-driven decision-making, Structured Query Language (SQL) serves as a fundamental tool for effective data analysis and business intelligence. SQL empowers organizations to extract valuable insights from vast datasets, enabling informed strategic decisions. Its versatility in handling complex queries, aggregations, and transformations makes it indispensable in navigating the complexities of a data-centric environment.
This project focuses on analyzing sales data using SQL, aiming to uncover patterns, trends, and key performance indicators, contributing valuable information for informed business decisions and strategic planning.
This dataset consists of 10 columns:
Order ID – A unique ID for each order placed on a product
Product – Item purchased
Quantity Ordered – Describes how many of that products are ordered
Price Each – Unit price of product
Order Date – Date on which the order is placed
Purchase Address – Address to where the order is shipped
Month, Sales, City, Hour – Extra attributes formed from the above.
Below is a sample of the data
| Order ID | Product | Quantity Ordered | Price Each | Order Date | Purchase Address | Month | Sales | City | Hour |
|---|---|---|---|---|---|---|---|---|---|
| 295665 | Macbook Pro Laptop | 1 | 1700.0 | 2019-12-30 00:01:00 | 136 Church St, New York City, NY 10001 | 12 | 1700.0 | New York City | 0 |
| 295666 | LG Washing Machine | 1 | 600.0 | 2019-12-29 07:03:00 | 562 2nd St, New York City, NY 10001 | 12 | 600.0 | New York City | 7 |
| 295669 | USB-C Charging Cable | 1 | 11.95 | 2019-12-18 12:38:00 | 43 Hill St, Atlanta, GA 30301 | 12 | 11.95 | Atlanta | 12 |
| 295668 | 27in FHD Monitor | 1 | 149.99 | 2019-12-22 15:13:00 | 410 6th St, San Francisco, CA 94016 | 12 | 149.99 | San Francisco | 15 |
| 295667 | USB-C Charging Cable | 1 | 11.95 | 2019-12-12 18:21:00 | 277 Main St, New York City, NY 10001 | 12 | 11.95 | New York City | 18 |
| 295670 | AA Batteries (4-pack) | 1 | 3.84 | 2019-12-31 22:58:00 | 200 Jefferson St, New York City, NY 10001 | 12 | 3.84 | New York City | 22 |
Because of the inherent presence of strings in databases, we start by converting these strings into numerical values.
update Sales_Data set month = cast(month as integer);
First, let’s see the total monthly sales to get an idea of the scope.
select Month, round(sum(QuantityOrdered * PriceEach), 2) as MonthlySale from Sales_Data group by Month;
| Month | MonthlySale |
|---|---|
| 1 | 1822256.73 |
| 2 | 2202022.42 |
| 3 | 2807100.38 |
| 4 | 3390670.24 |
| 5 | 3152606.75 |
| 6 | 2577802.26 |
| 7 | 2647775.76 |
| 8 | 2244467.88 |
| 9 | 2097560.13 |
| 10 | 3736726.88 |
| 11 | 3199603.2 |
| 12 | 4613443.34 |
We see a general increasing trend in sales over time. This is a positive indicator for future prospects, and it instills confidence in stakeholders, fosters a positive perception among consumers.
We are interested in where the sales come from, so we determine the popularity of products by finding out their sale quantity.
select Product, sum(QuantityOrdered) as Quantity
from Sales_Data group by Product order by Quantity desc;
| Product | Quantity |
|---|---|
| AAA Batteries (4-pack) | 31017 |
| AA Batteries (4-pack) | 27635 |
| USB-C Charging Cable | 23975 |
| Lightning Charging Cable | 23217 |
| Wired Headphones | 20557 |
| Apple Airpods Headphones | 15661 |
| Bose SoundSport Headphones | 13457 |
| 27in FHD Monitor | 7550 |
| iPhone | 6849 |
| 27in 4K Gaming Monitor | 6244 |
| 34in Ultrawide Monitor | 6199 |
| Google Phone | 5532 |
| Flatscreen TV | 4819 |
| Macbook Pro Laptop | 4728 |
| ThinkPad Laptop | 4130 |
| 20in Monitor | 4129 |
| Vareebadd Phone | 2068 |
| LG Washing Machine | 666 |
| LG Dryer | 646 |
Batteries and charging cables contributed most to the sale quantities, which is not surprising, given that they are consumables and has relatively low price.
It’s also important to find out which products generated the highest revenue. This information could enable business to refine their product offerings, optimize marketing strategies, and focus resources on the most lucrative segments of their portfolio：
select Product, round(sum(QuantityOrdered * PriceEach), 2) as PerProductSale
from Sales_Data group by Product order by PerProductSale desc;
| Product | PerProductSale |
|---|---|
| Macbook Pro Laptop | 8037600.0 |
| iPhone | 4794300.0 |
| ThinkPad Laptop | 4129958.7 |
| Google Phone | 3319200.0 |
| 27in 4K Gaming Monitor | 2435097.56 |
| 34in Ultrawide Monitor | 2355558.01 |
| Apple Airpods Headphones | 2349150.0 |
| Flatscreen TV | 1445700.0 |
| Bose SoundSport Headphones | 1345565.43 |
| 27in FHD Monitor | 1132424.5 |
| Vareebadd Phone | 827200.0 |
| 20in Monitor | 454148.71 |
| LG Washing Machine | 399600.0 |
| LG Dryer | 387600.0 |
| Lightning Charging Cable | 347094.15 |
| USB-C Charging Cable | 286501.25 |
| Wired Headphones | 246478.43 |
| AA Batteries (4-pack) | 106118.4 |
| AAA Batteries (4-pack) | 92740.83 |
| Macbook Pro Laptop | 8037600.0 |
Laptops and smartphones emerged as the primary drivers of sales, with a noteworthy emphasis on Apple products, a trend that finds justification in their elevated unit prices.
This trend may or may not be true in different cities, we can add a filter to see sales specific to different cities (e.g. where city = ‘Dallas’) or list all cities:
select Product, City, round(sum(Sales),2) as PerProductSale
from Sales_Data
group by Product, City
order by City, PerProductSale desc；
| Product | City | PerProductSale |
|---|---|---|
| Lightning Charging Cable | Atlanta | 28091.05 |
| USB-C Charging Cable | Atlanta | 22884.25 |
| Wired Headphones | Atlanta | 18932.21 |
| AA Batteries (4-pack) | Atlanta | 8421.12 |
(this is a short sample due to size)
It helps to know peak hours:
select Hour, round(sum(Sales), 2) as SaleAmount from Sales_Data
group by Hour order by Hour+0 asc;
| Hour | SaleAmount |
|---|---|
| 0 | 713721.27 |
| 1 | 460866.88 |
| 2 | 234851.44 |
| 3 | 145757.89 |
| 4 | 162661.01 |
| 5 | 230679.82 |
| 6 | 448113.0 |
| 7 | 744854.12 |
| 8 | 1192348.97 |
| 9 | 1639030.58 |
| 10 | 1944286.77 |
| 11 | 2300610.24 |
| 12 | 2316821.34 |
| 13 | 2155389.8 |
| 14 | 2083672.73 |
| 15 | 1941549.6 |
| 16 | 1904601.31 |
| 17 | 2129361.61 |
| 18 | 2219348.3 |
| 19 | 2412938.54 |
| 20 | 2281716.24 |
| 21 | 2042000.86 |
| 22 | 1607549.21 |
| 23 | 1179304.44 |
Knowing the specific time periods when sales experience a surge can significantly inform sales strategies and resource allocation. Recognizing peak sales hours allows businesses to optimize staffing levels and target marketing efforts, which helps capitalize on increased customer engagement, leading to higher conversion rates and revenue generation.
