Sales Dataset for Analysis and Practice

• sales.customer_id →references customers.customer_idmany to one: each Sales row points to one Customers row
• sales.product_id →references products.product_idmany to one: each Sales row points to one Products row

1. What is the total revenue generated by each product category?

   Join sales and products tables on product_id, then group by products.category and sum sales.total_sale_amount.

   tables: sales, products

2. Which customers are repeat buyers and what is their average purchase value?

   Join sales and customers tables on customer_id. Group by customer_id and count the number of sales. Filter for customers with more than one sale and calculate the average total_sale_amount.

   tables: sales, customers

3. Analyze the seasonality of sales over different periods.

   Extract the month and year from sale_date. Create a pivot table or use GROUP BY to show total sales by month and year.

   tables: sales

4. What is the average discount percentage applied to sales?

   Calculate the average of the discount_percent column in the sales table.

   tables: sales

Table names match the SQLite file and the SQL script.

SELECT p.category, SUM(s.total_sale_amount) AS total_revenue
FROM sales s
JOIN products p ON s.product_id = p.product_id
GROUP BY p.category
ORDER BY total_revenue DESC
LIMIT 20;

SELECT c.customer_id, c.first_name, c.last_name, SUM(s.total_sale_amount) AS total_spent
FROM customers c
JOIN sales s ON c.customer_id = s.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_spent DESC
LIMIT 20;

SELECT STRFTIME('%Y-%m', s.sale_date) AS sale_month, SUM(s.total_sale_amount) AS monthly_revenue
FROM sales s
GROUP BY sale_month
ORDER BY sale_month
LIMIT 20;

SELECT product_id, AVG(unit_price) AS average_unit_price
FROM sales
GROUP BY product_id
ORDER BY average_unit_price DESC
LIMIT 20;

Excel

Load each table into a separate sheet. Use Power Query to join tables based on their respective IDs (e.g., customer_id, product_id). Create a PivotTable from the joined sales data to analyze revenue by product category or customer segment.

Power BI

Create relationships between the customers, products, and sales tables using the customer_id and product_id keys to establish a star schema. Develop DAX measures for Total Sales (SUM(sales[total_sale_amount])) and Average Discount (AVERAGE(sales[discount_percent])).

SQL

Load the sales, customers, and products tables into your SQL database. Use JOIN clauses on customer_id and product_id to combine data for analysis. For example, to find total sales per category: SELECT p.category, SUM(s.total_sale_amount) FROM sales s JOIN products p ON s.product_id = p.product_id GROUP BY p.category.

Python

Use pandas to load the CSV files. Merge the dataframes on common keys (customer_id, product_id). Perform aggregations and analyses using pandas functions like groupby(), agg(), and merge().

• CSV (zip)One CSV file per table, zipped
• Excel workbookOne worksheet per table
• SQLite databaseA ready-to-query database file with every table
• Parquet (zip)One Parquet file per table, zipped
• SQL scriptCREATE TABLE with primary and foreign keys, then INSERTs
• CSVA single CSV file
• JSONA single JSON file