13ytVBwv

1DROP TEMPORARY TABLE IF EXISTS tmpInvoice;
2CREATE TEMPORARY TABLE tmpInvoice (
3	invoiceId INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
4	customerId INT UNSIGNED NOT NULL,
5	purchaseDate DATE,
6	orderTotal DECIMAL (12,2)
7);
8
9DROP TEMPORARY TABLE IF EXISTS tmpCustomer;
10CREATE TEMPORARY TABLE tmpCustomer (
11	customerId INT UNSIGNED NOT NULL PRIMARY KEY,
12	customerName VARCHAR(128)
13);
14
15INSERT INTO tmpCustomer (customerId, customerName) VALUES
16(78, 'Hydro Shoe'),
17(349, 'Pizza Vortex'),
18(132, 'Cashew Sanitizer, Inc.');
19
20INSERT INTO tmpInvoice (customerId, purchaseDate, orderTotal) VALUES
21(78, '2016-06-09', 133.99),
22(78, '2016-03-02', 45.99),
23(78, '2017-12-03', 78.99),
24(349, '2016-04-11', 134.99),
25(349, '2017-08-21', 935.99),
26(349, '2018-01-30', 546.99),
27(132, '2017-09-17', 657.99),
28(132, '2017-10-23', 268.99);
29
30SELECT
31	MAX(c.customerName) as customerName,
32	SUM(i.orderTotal) as lifetimeSales,
33	IF((SELECT lifetimeSales) > 999.99, 'Key Customer', 'Standard Customer') as customerType
34FROM
35	tmpCustomer c
36	JOIN tmpInvoice i
37		ON c.customerId=i.customerId
38GROUP BY
39	c.customerId;