cmc/data-science

 1-- Data ETL business script in sqlite3
 2
 3-- Create new field(s) for business-requested calculations
 4ALTER TABLE invoice_items
 5ADD COLUMN TotalPrice
 6GENERATED ALWAYS AS (UnitPrice * Quantity);
 7
 8-- Extract and summarize data to show sales totals per employee
 9WITH cst AS (
10	SELECT
11		CustomerId
12		,SupportRepId
13		,State
14		,Country
15	FROM customers
16),
17emp AS (
18	SELECT
19		EmployeeId
20		,LastName
21		,FirstName
22		,Title
23	FROM employees
24),
25inv AS (
26	SELECT
27		InvoiceId
28		,CustomerId
29		,InvoiceDate
30	FROM invoices
31),
32tot AS (
33	SELECT
34		InvoiceId
35		,UnitPrice
36		,Quantity
37		,TotalPrice
38	FROM invoice_items
39)
40
41SELECT DISTINCT * FROM emp
42LEFT JOIN cst ON emp.EmployeeId = cst.SupportRepId
43LEFT JOIN inv ON cst.CustomerId = inv.CustomerId
44LEFT JOIN tot ON inv.InvoiceId = tot.InvoiceId
45WHERE inv.InvoiceDate <= date()
46GROUP BY EmployeeId;
47
48-- Drop col used for calculations
49ALTER TABLE invoice_items DROP TotalPrice;