Invoice Dataset for Financial Analysis

• invoices.customer_id →references customers.customer_idmany to one: each Invoices row points to one Customers row
• line_items.invoice_id →references invoices.invoice_idmany to one: each Line items row points to one Invoices row
• line_items.product_id →references products.product_idmany to one: each Line items row points to one Products row

1. What is the total invoice amount for each customer?

   Join invoices and customers tables on customer_id and group by customer.

   tables: invoices, customers

2. Which products or services are most frequently invoiced?

   Join line_items and products tables on product_id and count occurrences of product_name.

   tables: line_items, products

3. How does the average invoice value change over different periods?

   Group invoices by year or month using invoice_date and calculate the average total_amount.

   tables: invoices

4. Create a pivot table showing total invoice amounts by customer state and invoice status.

   Join invoices and customers, then group by state and status, summing total_amount.

   tables: invoices, customers

Table names match the SQLite file and the SQL script.

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

SELECT p.product_name, COUNT(li.product_id) AS invoice_count
FROM line_items li
JOIN products p ON li.product_id = p.product_id
GROUP BY p.product_name
ORDER BY invoice_count DESC
LIMIT 5;

SELECT STRFTIME('%Y-%m', invoice_date) AS invoice_month, AVG(total_amount) AS average_invoice_amount
FROM invoices
GROUP BY invoice_month
ORDER BY invoice_month
LIMIT 20;

SELECT c.customer_id, c.email, COUNT(i.invoice_id) AS invoice_count, RANK() OVER (ORDER BY COUNT(i.invoice_id) DESC) as customer_rank
FROM customers c
JOIN invoices i ON c.customer_id = i.customer_id
GROUP BY c.customer_id, c.email
LIMIT 20;

Excel

Each table is provided as a separate sheet. Use Power Query to join tables or XLOOKUP for lookups. Create a PivotTable on the 'invoices' sheet, using 'customers.state' and 'invoices.status' as rows/columns and 'total_amount' as values.

Power BI

Load all tables. Create relationships: customers.customer_id to invoices.customer_id, and products.product_id to line_items.product_id. Create measures like Total Sales = SUM(invoices[total_amount]) and Average Invoice Value = AVERAGE(invoices[total_amount]).

SQL

Load the SQLite or SQL relational download. Use foreign keys to join tables: invoices.customer_id to customers.customer_id, and line_items.invoice_id to invoices.invoice_id, line_items.product_id to products.product_id.

Python

Use pandas to load CSV or Parquet files. Merge tables using pd.merge() and perform analysis. Example: df_invoices = pd.read_csv('invoices.csv'); df_customers = pd.read_csv('customers.csv'); merged_df = pd.merge(df_invoices, df_customers, on='customer_id').

• 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