1
2
3
4

SELECT drug, total_sales - cogs AS total_profit
FROM pharmacy_sales
ORDER BY total_profit DESC
LIMIT 3

1
2
3
4
5
6
7
8
9

WITH total_sales AS (
  SELECT manufacturer, drug, total_sales - cogs AS total_loss
  FROM pharmacy_sales
)
SELECT manufacturer, COUNT(drug) AS drug_count, ABS(SUM(total_loss)) AS total_loss
FROM total_sales
WHERE total_loss <= 0
GROUP BY manufacturer
ORDER BY total_loss DESC

1
2
3
4
5
6
7
8

WITH total_sales AS (
  SELECT manufacturer, ROUND(SUM(total_sales) / 1000000, 0) AS sale
  FROM pharmacy_sales
  GROUP BY manufacturer
  ORDER BY sale DESC
)
SELECT manufacturer, FORMAT('$%s million', sale) AS sale
FROM total_sales