add basic sqlite3 analysis script

1 files changed, 49 insertions, 0 deletions

diff --git a/projects/sqlite3-analysis/employee_sales.sql b/projects/sqlite3-analysis/employee_sales.sql
new file mode 100644
index 0000000..3336f8c
--- /dev/null
+++ b/projects/sqlite3-analysis/employee_sales.sql
@@ -0,0 +1,49 @@
+-- Data ETL business script in sqlite3
+
+-- Create new field(s) for business-requested calculations
+ALTER TABLE invoice_items
+ADD COLUMN TotalPrice
+GENERATED ALWAYS AS (UnitPrice * Quantity);
+
+-- Extract and summarize data to show sales totals per employee
+WITH cst AS (
+	SELECT
+		CustomerId
+		,SupportRepId
+		,State
+		,Country
+	FROM customers
+),
+emp AS (
+	SELECT
+		EmployeeId
+		,LastName
+		,FirstName
+		,Title
+	FROM employees
+),
+inv AS (
+	SELECT
+		InvoiceId
+		,CustomerId
+		,InvoiceDate
+	FROM invoices
+),
+tot AS (
+	SELECT
+		InvoiceId
+		,UnitPrice
+		,Quantity
+		,TotalPrice
+	FROM invoice_items
+)
+
+SELECT DISTINCT * FROM emp
+LEFT JOIN cst ON emp.EmployeeId = cst.SupportRepId
+LEFT JOIN inv ON cst.CustomerId = inv.CustomerId
+LEFT JOIN tot ON inv.InvoiceId = tot.InvoiceId
+WHERE inv.InvoiceDate <= date()
+GROUP BY EmployeeId;
+
+-- Drop col used for calculations
+ALTER TABLE invoice_items DROP TotalPrice;