How to collect statistics for different periods of time?

Question

SQL

I have a database in which I store information about the replenishment and the date of replenishment. I know how to calculate replenishment for 24 hours. And how to calculate at once for 24, a week and a month? One request.

SELECT `id_user`, SUM(`sum`) `sum`, users.name FROM `balance_history` INNER JOIN `users` ON `id_user` = users.id WHERE `date` >= NOW() - INTERVAL 1 DAY AND `type` = 'plus' GROUP BY `id_user` ORDER BY `sum` DESC

Answer 1

Use conditional aggregation.

SELECT `id_user`, 
       SUM(`sum`) `sum_month`, 
       SUM(`sum` * (`date` >= NOW() - INTERVAL 1 WEEK)) `sum_week`, 
       SUM(`sum` * (`date` >= NOW() - INTERVAL 1 DAY)) `sum_day`, 
       users.name 
FROM `balance_history` 
INNER JOIN `users` ON `id_user` = users.id 
WHERE `date` >= NOW() - INTERVAL 1 MONTH
  AND `type` = 'plus' 
GROUP BY `id_user`, users.name 
ORDER BY `sum` DESC

SUM() argument expressions are a simplified expression like

SUM(CASE WHEN {some condition}
         THEN {some value}
         ELSE 0
         END)

Well, WHERE also allows you not to shake older records, which are obviously not taken into account in any of the sums. Therefore, it selects the longest of the summation periods, and at the same time eliminates the aggregation condition from one of the expressions.