Restaurant Sales Dataset for Business Analysis

• orders.customer_id →references customers.customer_idmany to one: each Orders row points to one Customers row
• orders.item_id →references menu_items.item_idmany to one: each Orders row points to one Menu items row

1. What are the most popular menu items by quantity sold?

   Join orders and menu_items, then group by item_name and sum quantity.

   tables: orders, menu_items

2. Which hours of the day have the highest order volume?

   Extract the hour from order_date, then group by hour and count orders.

   tables: orders

3. Calculate the average order value per customer.

   Join orders and customers, group by customer_id, and calculate the average of line_total.

   tables: orders, customers

4. Analyze sales trends by customer segment and loyalty tier.

   Join orders and customers, group by customer_segment and loyalty_tier, and sum line_total.

   tables: orders, customers

Table names match the SQLite file and the SQL script.

SELECT mi.item_name, SUM(o.quantity) AS total_quantity
FROM orders o
JOIN menu_items mi ON o.item_id = mi.item_id
GROUP BY mi.item_name
ORDER BY total_quantity DESC
LIMIT 20;

SELECT c.customer_segment, SUM(o.line_total) AS total_sales
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY c.customer_segment
ORDER BY total_sales DESC
LIMIT 20;

SELECT c.loyalty_tier, AVG(o.line_total) AS average_order_value
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY c.loyalty_tier
ORDER BY average_order_value DESC
LIMIT 20;

SELECT order_date, SUM(line_total) AS daily_sales
FROM orders
GROUP BY order_date
ORDER BY order_date
LIMIT 20;

Excel

Load each table into a separate Excel sheet. Use Power Query to join tables (e.g., orders with menu_items on item_id). Create a PivotTable from the joined data to analyze sales by category or customer segment.

Power BI

Load all tables. Create relationships: orders.customer_id to customers.customer_id, and orders.item_id to menu_items.item_id. Create measures like Total Sales = SUM(orders[line_total]) and Average Order Quantity = AVERAGE(orders[quantity]).

SQL

Load the SQLite or SQL relational download. The primary keys are customer_id, item_id, and order_id. Join tables using these keys, for example, `FROM orders o JOIN customers c ON o.customer_id = c.customer_id`.

Python

Use pandas to load CSV or Parquet files. Join tables using `pd.merge()` and perform aggregations with `.groupby()`. For example: `orders.merge(menu_items, on='item_id').groupby('item_name')['quantity'].sum()`.

• 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