# total sales (quantity) by country
SELECT a.country, SUM(CASE WHEN c.quantity is not null THEN c.quantity ELSE 0 END) AS total_sales
FROM zyz_customers a
LEFT JOIN zyz_Orders b
ON a.customerid=b.customerid
LEFT JOIN zyz_Orderdetails c
ON b.orderid=c.orderid
GROUP BY 1
ORDER BY 1
# revenue by country
SELECT a.country, SUM(CASE WHEN c. quantity is not null then c.quantity*d.price ELSE 0 END) as revenue
FROM zyz_Customers a
LEFT JOIN zyz_Orders b
ON a.customerid=b.customerid
LEFT JOIN zyz_Orderdetails c
ON b.orderid=c.orderid
LEFT JOIN zyz_Products d
ON c.productid=d.productid
GROUP BY 1
ORDER BY 1
# revenue by category in Austria
SELECT a.country, d.categoryid, SUM(CASE WHEN c.quantity is not null then c.quantity*d.price ELSE 0 END) as revenue
FROM zyz_Customers a
JOIN zyz_Orders b
ON a.customerid=b.customerid
JOIN zyz_Orderdetails c
ON b.orderid=c.orderid
JOIN zyz_Products d
ON c.productid=d.productid
WHERE a.country='Austria'
GROUP BY 1,2
ORDER BY 1,2
#average spend per customer (total_spend/total_order)
SELECT a.customerid, CASE WHEN COUNT(DISTINCT b.orderid)>0 then SUM(c.quantity*d.price)/COUNT(DISTINCT b.orderid) ELSE null end as average
FROM zyz_customers as a
JOIN zyz_orders b ON a.customerid=b.customerid
JOIN zyz_orderdetails c ON b.orderid=c.orderid
JOIN zyz_products d ON c.productid=d.productid
GROUP BY 1
ORDER BY 1
# top 5 most popular products in each category
# in interview, should ask 'how is popular defined' and in this case, it's the quantity
SELECT categoryid, productid, sales, row_num
FROM (
SELECT a.categoryid, c.productid, c.sales, row_number() over (partition by a.categoryid order by c.sales desc) as row_num
FROM zyz_categories a
JOIN
(SELECT DISTINCT categoryid, productid
FROM zyz_products) AS b on a.categoryid=b.categoryid
JOIN
(SELECT productid, sum(quantity) as sales
FROM zyz_orderdetails
GROUP BY 1) AS c on b.productid=c.productid
) AS fun
WHERE row_num<=5
ORDER BY 1,4
# Option 2
SELECT *
FROM (
SELECT categoryid, productid, sales, row_number() over (partition by categoryid order by sales desc) as row_num
FROM (SELECT a.categoryid, a.productid, SUM(b.quantity) as sales
FROM zyz_products a
INNER JOIN zyz_orderdetails b
ON a.productid=b.productid
GROUP by 1,2) as fun
) as fun1
WHERE row_num<=5
ORDER BY 1,4
SELECT a.customerid,
SUM(CASE WHEN e.categoryname='Beverages' then c.quantity else 0 END) as bev_sales,
SUM(CASE WHEN e.categoryname='Condiments' then c.quantity else 0 END) as condi_sales,
SUM(CASE WHEN e.categoryname not in ('Beverage', 'Condiments') then c.quantity else 0 END) as other_sales
FROM zyz_customers a
INNER JOIN zyz_orders b ON a.customerid=b.customerid
INNER JOIN zyz_orderdetails c ON b.orderid=c.orderid
INNER JOIN zyz_products d ON c.productid=d.productid
INNER JOIN zyz_categories e ON d.categoryid=e.categoryid
GROUP BY 1
ORDER BY 1
