[Info-vax] CRTL and RMS vs SSIO

[Arne Vajhøj]

On 10/13/2021 10:54 PM, Dave Froble wrote:
> Task definition
> 
> Select customers with shipped orders
> Select orders with total dollars shipped >= 10
> Sort by customer name, then by dollars, descending
> Limit output to 3 orders per customer
> print customer name and total dollars per order
> 
> Is that it?

Close.

- aggregate sales by customer for only orderes delivered
- filter so only customers where total sales > 10
- sort sales descending and pick top 3

SQL decomposition/evolution:

-- basic data
SELECT customers.name,orderlines.price
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
LEFT JOIN orderlines ON orders.order_id = orderlines.order_id;
A company|10
A company|20
A company|30
A company|35
A company|40
A company|20
A company|25
A company|30
A company|10
A company|15
B company|5
B company|5
B company|10
B company|5
B company|10
B company|10
B company|10
B company|20
C company|20
C company|40
C company|80
C company|160
C company|320
D company|80
D company|160
E company|
-- add aggregation
SELECT customers.name,SUM(orderlines.price) AS totalsales
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
LEFT JOIN orderlines ON orders.order_id = orderlines.order_id
GROUP BY customers.name;
A company|235
B company|75
C company|620
D company|240
E company|
-- add condition to only consider delivered orders
SELECT customers.name,SUM(orderlines.price) AS totalsales
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
LEFT JOIN orderlines ON orders.order_id = orderlines.order_id
WHERE orders.status = 'Delivered'
GROUP BY customers.name;
A company|135
B company|75
C company|620
D company|240
-- add condition to only show rows with more than 10 in total sales
SELECT customers.name,SUM(orderlines.price) AS totalsales
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
LEFT JOIN orderlines ON orders.order_id = orderlines.order_id
WHERE orders.status = 'Delivered'
GROUP BY customers.name
HAVING totalsales > 10;
A company|135
B company|75
C company|620
D company|240
-- add sort descending by total sales
SELECT customers.name,SUM(orderlines.price) AS totalsales
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
LEFT JOIN orderlines ON orders.order_id = orderlines.order_id
WHERE orders.status = 'Delivered'
GROUP BY customers.name
HAVING totalsales > 10
ORDER BY totalsales DESC;
C company|620
D company|240
A company|135
B company|75
-- add limit to 3 output rows
SELECT customers.name,SUM(orderlines.price) AS totalsales
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
LEFT JOIN orderlines ON orders.order_id = orderlines.order_id
WHERE orders.status = 'Delivered'
GROUP BY customers.name
HAVING totalsales > 10
ORDER BY totalsales DESC
LIMIT 3;
C company|620
D company|240
A company|135

Arne