Comments (4)
Paul Buffa
Jun 28, 2023Very close! You just needed to use SUM instead of COUNT in the final select statement.
Paul Buffa
Jun 28, 2023Just to note, you can use the "::" shorthand for casting as a different data type, so here, it would be:
orderDate::date
Paul Buffa
Jun 28, 2023Nice work on the assignment and for going after the bonus points! You've completed the SQL course. We'll send over your certificate soon, we're just wrapping up going over the assignments.
The solution I had for the bonus points is a bit different, as it looks at the combination of items ordered and sees how much variation there is. I've pasted in the comment below if you want to take a look.
Jun 28, 2023
WITH customerOrders AS (
SELECT customerId
, orders.id
, group_concat(order_items.SKU, ',') as full_order
, count(distinct orders.id) as num_orders
FROM orders
JOIN order_items
ON orders.id = order_items.orderId
GROUP BY 1, 2
), customerOrderCounts AS (
SELECT customerId
, count(distinct id) as num
FROM orders
GROUP BY 1
HAVING num > 1
), orderFrequency AS (
SELECT customerId
, full_order
, count(distinct id) as num
FROM customerOrders
WHERE customerId in (SELECT DISTINCT customerId FROM customerOrderCounts)
GROUP BY 1, 2
)
SELECT customerOrderCounts.num as "Number of Orders"
, count(distinct customerOrderCounts.customerId) as "Number of Customers"
, median(orderFrequency.num) as "Median Number of Distinct Orders"
, avg(orderFrequency.num) as "Avg Number of Distinct Orders"
, max(orderFrequency.num) as "Max Number of Distinct Orders"
, min(orderFrequency.num) as "Min Number of Distinct Orders"
FROM orderFrequency
JOIN customerOrderCounts
ON orderFrequency.customerId = customerOrderCounts.customerId
GROUP BY 1
ORDER BY 1