Homework 5 - alajal/soft6 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 data.*
FROM
(SELECT sum(quantity * itemprice) total_price
FROM "PUBLIC"."SOLDITEM" si
GROUP BY name
ORDER BY total_price desc
LIMIT 1) max_price,
(SELECT name, 
sum(quantity) as total_quantity,
itemprice as unit_price, 
sum(quantity * itemprice) as total_price
FROM "PUBLIC"."SOLDITEM" si
GROUP BY name, itemprice) data

WHERE data.total_price = max_price.total_price

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

SELECT
CAST(sale.sale_date AS DATE) as sale_date
FROM "PUBLIC"."SOLDITEM" si, "PUBLIC"."SALE" sale
WHERE si.sale_id = sale.id
GROUP BY sale.sale_date
ORDER BY  sum(quantity * itemprice) DESC, sale_date
LIMIT 1