Hi,
I have searched in codeacademy forum to compare my solution for this project ( seen: Database Normalization at Fred's Furniture - #2), but I believe that and my solution are not completely correct because step 16 and 17 do not produce the same result. Moreover, I am interested in the proper solution of step 18.
My Solution:
16.
WITH all_items AS (
SELECT item_1_id as item_id
FROM store
UNION ALL
SELECT item_2_id as item_id
FROM store
WHERE item_2_id IS NOT NULL
UNION ALL
SELECT item_3_id as item_id
FROM store
WHERE item_3_id IS NOT NULL
)
SELECT item_id, COUNT(*)
FROM all_items
GROUP BY item_id
order by item_id asc;
select item_id, count(order_id) from orders_items
group by item_id order by item_id asc;
with ntable as(
select distinct(customers.customer_id) as customer, count(orders.order_id) as t_order
from customers
join orders
on customers.customer_id = orders.customer_id
group by customers.customer_id
having count(orders.order_id) > 1)
select count(customer) from ntable;
select count(orders.order_id) as total_lamp_order
from orders
join orders_items as oi
on orders.order_id = oi.order_id
join items
on items.item_id = oi.item_id
where items.name = ‘lamp’
and orders.order_date > ‘2019-07-15’;
select count(orders.order_id) as total_chair_order
from orders
join orders_items as oi
on orders.order_id = oi.order_id
join items
on items.item_id = oi.item_id
where items.name = ‘chair’
and orders.order_date > ‘2019-07-15’;
Regards