צור קשר

אתגר SQL מספר 6# - ההזמנות מהקטגוריה הגבוהה ביותר

בעבר יצרתי פלייליסט ביוטיוב עם אתגרי SQL ופתרונות. בשבוע שעבר התיישבתי להקליט פרק נוסף, אבל הפעם עם כמה טוויסטים:

1. את הסרטון צילמתי ב-One Shot, בלי עצירות או עריכות.
2. את התרגיל? גיליתי רק ברגע שבו התחלתי לפתור אותו – פשוט ביקשתי מצ'ט ג'יפיטי שיג'נרט לי אתגר ויצאתי לדרך.

אני מאמין שיש המון ערך מוסף בלראות מישהו מתמודד עם אתגר בזמן אמת – טועה, מתקן, ומסביר את החשיבה שמאחורי הפתרון. זה לא תמיד נראה "חלק", ומצטלם יפה, אבל זו בדיוק המציאות שממנה לומדים הכי הרבה. תהנו 🙂

התרגיל

נתונה טבלה המתארת הזמנות מלקוחות. עליכם לכתוב שאילתה שמחזירה את כל הלקוחות שביצעו הזמנה שבה כל המוצרים שהוזמנו הם מהקטגוריה היקרה ביותר בטבלה (הקטגוריה בעלת המחיר הממוצע הגבוה ביותר).

עליכם להחזיר את המידע הבא:

  • שם הלקוח
  • מזהה הזמנה
  • קטגוריה
  • סך כל ההוצאה על ההזמנה

סקריפט ליצירת הטבלה 

				
					<xmp>DROP TABLE IF EXISTS Orders;
CREATE TABLE Orders (
    OrderID INT,
    CustomerName VARCHAR(100),
    ProductName VARCHAR(100),
    Category VARCHAR(50),
    Price DECIMAL(10, 2),
    Quantity INT
);

INSERT INTO Orders (OrderID, CustomerName, ProductName, Category, Price, Quantity)
VALUES
(1, 'Alice', 'Laptop', 'Electronics', 1000.00, 1),
(1, 'Alice', 'Mouse', 'Electronics', 50.00, 2),
(2, 'Bob', 'T-Shirt', 'Clothing', 20.00, 3),
(2, 'Bob', 'Jeans', 'Clothing', 40.00, 1),
(3, 'Charlie', 'Smartphone', 'Electronics', 800.00, 1),
(3, 'Charlie', 'Charger', 'Electronics', 25.00, 1),
(4, 'Diana', 'Notebook', 'Stationery', 5.00, 4),
(4, 'Diana', 'Pen', 'Stationery', 2.00, 10),
(5, 'Eve', 'Dress', 'Clothing', 100.00, 1),
(5, 'Eve', 'Shoes', 'Clothing', 150.00, 1),
(6, 'Frank', 'Monitor', 'Electronics', 300.00, 2),
(6, 'Frank', 'Keyboard', 'Electronics', 80.00, 1),
(7, 'Grace', 'Backpack', 'Stationery', 30.00, 1),
(7, 'Grace', 'Laptop', 'Electronics', 1000.00, 1);
</xmp>

פתרונות

				
					<xmp>-- By Ram Kedem as shown in the video
;WITH highest_cat AS 
	(
	SELECT category, AVG(price) AS avg_price, 
					 DENSE_RANK() OVER (ORDER BY AVG(price) DESC) AS 'dr'
	FROM   orders 
	GROUP BY category
	)
SELECT   orderID, customerName, 
         COUNT(DISTINCT category) AS 'cnt_d', 
		 MAX(category) AS 'max_c',
		 SUM(price) AS 'total_price'
FROM     orders
GROUP BY orderID, customerName
HAVING   MAX(category) IN (SELECT category FROM highest_cat WHERE dr = 1)
AND      COUNT(DISTINCT category) = 1 </xmp>
				
			
				
					<xmp>-- Adjusted solution, taking the Quantity into account

;WITH highest_cat AS 
	(
	SELECT category, AVG(price) AS avg_price, 
					 DENSE_RANK() OVER (ORDER BY AVG(price) DESC) AS 'dr'
	FROM   orders 
	GROUP BY category
	)
SELECT   orderID, customerName, 
         COUNT(DISTINCT category) AS 'cnt_d', 
		 MAX(category) AS 'max_c',
		 SUM(price * quantity) AS 'total_price'
FROM     orders
GROUP BY orderID, customerName
HAVING   MAX(category) IN (SELECT category FROM highest_cat WHERE dr = 1)
AND      COUNT(DISTINCT category) = 1 </xmp>
				
			
				
					<xmp>-- By ChatGPT
WITH CategoryAvgPrice AS (
    SELECT
        Category,
        AVG(Price) AS AvgPrice
    FROM
        Orders
    GROUP BY
        Category
),
MaxCategory AS (
    SELECT
        Category
    FROM
        CategoryAvgPrice
    WHERE
        AvgPrice = (SELECT MAX(AvgPrice) FROM CategoryAvgPrice)
),
OrdersWithTotal AS (
    SELECT
        OrderID,
        CustomerName,
        Category,
        SUM(Price * Quantity) AS TotalPrice,
        COUNT(DISTINCT Category) AS DistinctCategories
    FROM
        Orders
    GROUP BY
        OrderID, CustomerName, Category
)
SELECT DISTINCT
    o.CustomerName,
    o.OrderID,
    o.Category,
    o.TotalPrice
FROM
    OrdersWithTotal o
INNER JOIN
    MaxCategory mc
ON
    o.Category = mc.Category
WHERE
    o.DistinctCategories = 1;
</xmp>

קורסים קרובים

קורסי אונליין

קורסים חינמיים

דברו איתנו