Skip to content

Instantly share code, notes, and snippets.

@kunxin-chor
Created March 30, 2022 04:13
Show Gist options
  • Save kunxin-chor/9f5c424f13ecaf0c5ae72a45cc62aa7c to your computer and use it in GitHub Desktop.
Save kunxin-chor/9f5c424f13ecaf0c5ae72a45cc62aa7c to your computer and use it in GitHub Desktop.
Find best selling product for each month
( SELECT productCode, year(orderDate) as orderYear, month(orderDate) as orderMonth
FROM orderdetails
JOIN orders on orders.orderNumber = orderdetails.orderNumber
GROUP BY productCode, YEAR(orderDate), MONTH(orderDate)
HAVING productCode = ( SELECT productCode
FROM orderdetails JOIN orders on orderdetails.orderNumber = orders.orderNumber
WHERE year(orderDate) = orderYear AND month(orderDate) = orderMonth
GROUP BY productCode
ORDER BY COUNT(*) DESC
LIMIT 1
)
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment