Getting date with max sales for a month

Question

I have a table in Hadoop that I can count the number of sales for a specific month

select count(*) from sales where sold_date between '2023-08-01' and '2023-08-31'

How can I update this query to get the date of the most sales in August instead of the count of sales for the whole month?

Answer 1

Try this :

SELECT sold_date, COUNT(*) as sales_count
FROM sales
WHERE sold_date BETWEEN '2023-08-01' AND '2023-08-31'
GROUP BY sold_date
ORDER BY sales_count DESC
LIMIT 1;