Product Dataset

• products.category_id →references categories.category_idmany to one: each Products row points to one Categories row
• products.supplier_id →references suppliers.supplier_idmany to one: each Products row points to one Suppliers row
• sales.product_id →references products.product_idmany to one: each Sales row points to one Products row

1. What is the total sales amount for each product category?

   Join products with categories and sales, then group by category name and sum sale amount.

   tables: products, categories, sales

2. Which suppliers provide products with the highest unit cost?

   Join products with suppliers and order by unit_cost in descending order.

   tables: products, suppliers

3. Analyze the distribution of sales amounts across different product prices.

   Join products and sales, then analyze the relationship between product price and sale_amount.

   tables: products, sales

4. Create a dashboard showing top-selling products by quantity sold over time.

   Join products and sales, group by product name and sale_date, and sum quantity_sold.

   tables: products, sales

Table names match the SQLite file and the SQL script.

SELECT c.category_name, SUM(s.sale_amount) AS total_sales
FROM sales s
JOIN products p ON s.product_id = p.product_id
JOIN categories c ON p.category_id = c.category_id
GROUP BY c.category_name
ORDER BY total_sales DESC
LIMIT 20;

SELECT su.supplier_name, AVG(p.unit_cost) AS average_unit_cost
FROM products p
JOIN suppliers su ON p.supplier_id = su.supplier_id
GROUP BY su.supplier_name
ORDER BY average_unit_cost DESC
LIMIT 5;

SELECT p.product_name, SUM(s.quantity_sold) AS total_quantity_sold
FROM sales s
JOIN products p ON s.product_id = p.product_id
GROUP BY p.product_name
ORDER BY total_quantity_sold DESC
LIMIT 20;

SELECT product_id, product_name, stock_quantity
FROM products
WHERE stock_quantity < 50
ORDER BY stock_quantity ASC
LIMIT 20;

Excel

Load each table into a separate sheet. Use XLOOKUP to join tables or Power Query for more complex data transformations. Create PivotTables on the sales data to analyze sales by category or over time.

Power BI

Model the data using a star schema: 'sales' as the fact table, and 'products', 'categories', and 'suppliers' as dimension tables. Create relationships between 'sales.product_id' and 'products.product_id', and 'products.category_id' and 'categories.category_id'. Example DAX Measures: Total Sales = SUM(sales[sale_amount]) Total Quantity Sold = SUM(sales[quantity_sold]) Average Unit Cost = AVERAGE(products[unit_cost])

SQL

Load the SQLite or SQL dump into your preferred database. The primary keys and foreign keys are defined in the schema, allowing for joins between tables like sales to products and products to categories or suppliers.

Python

Use pandas to load CSV or SQL data. Merge tables using `pd.merge()` and perform aggregations with `groupby()` and `agg()`. Utilize libraries like Matplotlib or Seaborn for visualizations.

• 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