1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
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;
|
