How to calculate average by day in Mysql?

Question

MySQL

Data available
+--------------------+----------+------+
| datetime | user_id | price |
+--------------------+----------+------+
| 2020-01-01 01:05:30 | 1 | 10 |
| 2020-01-01 02:45:33 | 3 | 20 |
| 2020-03-25 11:10:15 | 66 | 10 |
| 2020-04-21 00:40:00 | 21 | 40 |
| 2020-04-22 15:00:00 | 70 | 30 |
| 2020-04-22 20:00:00 | 21 | 10 |
+--------------------+----------+------+

Need to get grouped data every day in a particular way. Every day the sum for the previous days is calculated including the current one and COUNT(DISTINCT user_id) also for the previous days and including the current one
+-------------------------------------+----------- ----+
| day | COUNT(DISTINCT user_id) | SUM(price) |
+-------------------------------------+---------- ----+
i.e. for 2020-01-01 there will be the following COUNT(DISTINCT 1, 3) = 2, sum = 10+20
+---------------+- -+-----+
| 2020-01-01 | 2 | 30 |
+---------------+--+-----+
for 2020-04-22 will be COUNT(DISTINCT 1, 3, 66, 21, 70, 21) = 5 , sum = 10+20+10+40+30+10
+---------------+--+-----+
| 2020-01-01 | 5 | 120 |
+---------------+--+-----+

The amount grouped by days is calculated as follows:

SELECT sort_date, (@s := @s + temp.sum) sum 
  FROM (
    SELECT SUM(price) as sum, DATE(date_time) as sort_date
       FROM table
       GROUP sort_date
  ) temp

But I can't figure out how to add "COUNT(DISTINCT user_ID)" to this. Mysql version 5.7

Answer 1

You can use the following query:

select dates.date_time dates, count(distinct tbl.user_id), sum(tbl.price)
from (
  select distinct date(tbl.date_time) date_time from tbl
) dates
join tbl on dates.date_time >=  date(tbl.date_time)
group by dates.date_time
order by dates.date_time;

Test SQL query online