인기 상품 순위: 인기 상품을 순위로 나열하는 쿼리.

인기 상품 순위는 판매량, 조회수, 리뷰 등 다양한 기준을 통해 결정될 수 있으며, 이는 고객의 관심과 시장의 동향을 파악하는 데 중요합니다. 다음은 인기 상품을 순위로 나열하는 다양한 SQL 쿼리 예제와 각 예제의 간단한 설명입니다.


예제 1: 판매량 기준 인기 상품 순위
SELECT ProductID, SUM(Quantity) AS TotalSold FROM OrderDetails GROUP BY ProductID ORDER BY TotalSold DESC;
각 상품의 총 판매량을 기준으로 인기 순위를 나열합니다.

예제 2: 매출액 기준 인기 상품 순위
SELECT ProductID, SUM(TotalAmount) AS TotalRevenue FROM Orders JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID GROUP BY ProductID ORDER BY TotalRevenue DESC;
각 상품의 총 매출액을 기준으로 인기 순위를 나열합니다.

예제 3: 리뷰 수 기준 인기 상품 순위
SELECT ProductID, COUNT(ReviewID) AS NumberOfReviews FROM Reviews GROUP BY ProductID ORDER BY NumberOfReviews DESC;
각 상품의 총 리뷰 수를 기준으로 인기 순위를 나열합니다.

예제 4: 평균 평점 기준 인기 상품 순위
SELECT ProductID, AVG(Rating) AS AverageRating FROM Reviews GROUP BY ProductID ORDER BY AverageRating DESC;
각 상품의 평균 평점을 기준으로 인기 순위를 나열합니다.

예제 5: 최근 조회수 기준 인기 상품 순위
SELECT ProductID, COUNT(*) AS NumberOfViews FROM ProductViews WHERE ViewDate > DATEADD(month, -1, CURRENT_DATE) GROUP BY ProductID ORDER BY NumberOfViews DESC;
최근 한 달간 각 상품의 조회수를 기준으로 인기 순위를 나열합니다.

예제 6: 카테고리별 인기 상품 순위
SELECT Category, ProductID, SUM(Quantity) AS TotalSold FROM OrderDetails JOIN Products ON OrderDetails.ProductID = Products.ProductID GROUP BY Category, ProductID ORDER BY Category, TotalSold DESC;
각 카테고리 내에서의 인기 상품 순위를 나열합니다.

예제 7: 시즌별 인기 상품 순위
SELECT ProductID, SUM(Quantity) AS TotalSold FROM OrderDetails WHERE OrderID IN (SELECT OrderID FROM Orders WHERE OrderDate BETWEEN '2022-06-01' AND '2022-08-31') GROUP BY ProductID ORDER BY TotalSold DESC;
특정 시즌(예: 여름) 동안의 인기 상품 순위를 나열합니다.

예제 8: 할인된 상품 중 인기 상품 순위
SELECT ProductID, SUM(Quantity) AS TotalSold FROM OrderDetails WHERE ProductID IN (SELECT ProductID FROM Products WHERE Discount > 0) GROUP BY ProductID ORDER BY TotalSold DESC;
할인된 상품 중에서 인기 상품 순위를 나열합니다.

예제 9: 신상품 중 인기 상품 순위
SELECT ProductID, SUM(Quantity) AS TotalSold FROM OrderDetails JOIN Products ON OrderDetails.ProductID = Products.ProductID WHERE Products.AddedDate > DATEADD(year, -1, CURRENT_DATE) GROUP BY ProductID ORDER BY TotalSold DESC;
최근 1년 이내에 추가된 신상품 중에서 인기 상품 순위를 나열합니다.

예제 10: VIP 고객 구매 기준 인기 상품 순위
SELECT ProductID, SUM(Quantity) AS TotalSold FROM OrderDetails JOIN Orders ON OrderDetails.OrderID = Orders.OrderID WHERE CustomerID IN (SELECT CustomerID FROM VIPCustomers) GROUP BY ProductID ORDER BY TotalSold DESC;
VIP 고객들이 구매한 상품을 기준으로 인기 상품 순위를 나열합니다.

관련 전문용어 설명
판매량(Sales Volume): 상품이 판매된 총 수량입니다.
매출액(Revenue): 상품 판매로부터 발생한 총 수입입니다.
리뷰(Reviews): 소비자가 상품에 대해 남긴 평가와 의견입니다.
평균 평점(Average Rating): 리뷰에 기반한 상품의 평균 평가 점수입니다.
카테고리(Category): 상품이 분류되는 그룹 또는 분류입니다.
VIP 고객(VIP Customers): 특별한 혜택을 받는 중요 고객 그룹입니다.