Calculate difference between timestamp field with specific timestamp

Question

I’m trying to develop a ranking chart for a kind of questionnaire.

Each day a question is asked at 4pm, which can be answered until 5:59:59 the next day. The table has to show the position of the participants taking into account the number of right answers and time.

That means you answer right and faster you get first. The number of questions answered also counts, if Arque does not answer a day is penalized.

My chart will be like:

+-------+---------+---------------------+
|userid | acertou |      timestamp      |
+-------+---------+---------------------+
|   2   |    1    | 2018-02-07 16:00:01 |
|   1   |    1    | 2018-02-07 16:02:00 |
|   3   |    1    | 2018-02-07 17:00:00 |
|   1   |    0    | 2018-02-08 16:00:02 |
|   3   |    1    | 2018-02-08 16:00:05 |
|   2   |    0    | 2018-02-08 16:01:00 |
+-------+---------+---------------------+

I started with this query:

SELECT `userid`, `acertou`, `timestamp`, 
count(acertou) as cont 
FROM `resultados` 
WHERE acertou = 1
GROUP BY `userid `
ORDER BY cont DESC, timestamp DESC

But I realize that this is not what I want because the ranking has to be cumulative but taking into account the various days.

Does anyone have any idea how I can do this?

Answer 1

As in chat, your problem is that you don’t have a reference to the start date of the questions, so you can’t calculate the difference between the dates.

Having this table with the dates and questions, the select would be basically this way:

-- soma acertos, e verifica a diferença em horas, minutos, segundos
SELECT userid, SUM(acertou),
-- Diferença em horas
SUM(TIMESTAMPDIFF(HOUR,timestamp,CAST(CONCAT_WS(' ',date(timestamp), '17:59:59') as DATETIME))) horas,
-- Diferença em minutos
SUM(TIMESTAMPDIFF(MINUTE,timestamp,CAST(CONCAT_WS(' ',date(timestamp), '17:59:59') as DATETIME))) minutos,
-- Diferença em segundos
SUM(TIMESTAMPDIFF(SECOND,timestamp,CAST(CONCAT_WS(' ',date(timestamp), '17:59:59') as DATETIME))) segundos
FROM resultados
-- agrupa por usuário
GROUP BY userid
-- OPICIONAL, verifica se o usuário respondeu todas as perguntas
HAVING SUM(acert) = (SELECT COUNT(*) FROM (SELECT DISTINCT(perg_n) FROM resultados) qtd_perguntas)
-- ordena pelo que tem mais acertos, depois pelos tempos ( quanto mais tempo sobrou, menos tempo usou.
ORDER BY acertos DESC, horas DESC, minutos DESC, segundos DESC;