SQL - Searching for best-selling product in a date range

Question

I’m trying to search with SQL the best selling product within a certain period. For this, I have the table 'conta_products', which has the following columns:

id (PK) | conta_id (FK) | producto_id (FK) | precoFinal | created_at | updated_at

Well, I obviously have the table produto and the table contas.

Before I had already made an SQL that can fetch the best selling product using a 'Count', ordering descending and limiting the result to the best selling, as follows:

SELECT nome, urlImagem
FROM produtos
WHERE id = (
    SELECT produto_id
    FROM conta_produtos
    GROUP BY produto_id
    ORDER BY count(*) DESC
    LIMIT 1
);

But now I want to limit this SQL for a certain period, ie, search which product was the most sold in a certain date range.

Then I believe I have to change the SELECT from within. I changed it, I tried to elaborate a reasoning, but unfortunately SQL has some error and I can’t see what it is. My SQL:

SELECT produto_id
FROM conta_produtos
GROUP BY produto_id
ORDER BY count(*) DESC
WHERE updated_at BETWEEN '2017-03-03' AND '2017-03-08'  

What I realized was that if I put the last line (the main line of my sql, because it does the date filter), SQL does not work. Will this group by id catch the latest date between two equal Ids? Is that what’s getting in the way of my Where?

Thank you!

Answer 1

The WHERE clause should come before GROUP BY and ORDER BY. Example:

SELECT produto_id
FROM conta_produtos
WHERE updated_at BETWEEN '2017-03-03' AND '2017-03-08' 
GROUP BY produto_id
ORDER BY count(*) DESC

Answer 2

Apart from the fact that group by and order by should come at the end, after Where, it may be interesting to include the Count column with a nickname.

SELECT 
    produto_id, count(produto_id) as contagem_produto
FROM 
    conta_produtos
WHERE 
    updated_at BETWEEN '2017-03-03' AND '2017-03-08' 
GROUP BY 
    produto_id
ORDER BY 
    contagem_produto DESC