Homework 5 SQL queries - lauri0/POS GitHub Wiki

Query that returns the most popular product name, total quantity, unit price and unit price multiplied by the total quantity sold (most popular one is the one that gave highest income - last column max number)

select * from (select name, count() as Times_bought, sum(quantity) Total_quantity, itemprice, (itempricesum(quantity)) as Total_revenue from solditem group by name, itemprice) where Total_revenue = (select max(Total_revenue) from (select name, count(), sum(quantity), itemprice, (itempricesum(quantity)) as Total_revenue from solditem group by name, itemprice))

Query that returns the date with the highest income (should return only one row)

select * from (select purchase_date, sum(total_price) as total_sum from purchase group by purchase_date) where total_sum = (select max(total_sum) from (select purchase_date, sum(total_price) as total_sum from purchase group by purchase_date))