How to return AVG by date and by user [closed]

I need to perform a task that is quite complicated for me, I need to return the average number of records entered by users per day.

Lets say that i have this table

+----+--+---+--------+----------------------+
| id |  |   | random |     insert_date      |
+----+--+---+--------+----------------------+
|  1 |  | 1 | text   | 10/04/2021  00:00:00 |
|  2 |  | 1 | text   | 10/03/2021  00:00:00 |
|  3 |  | 1 | text   | 10/03/2021  00:00:00 |
|  4 |  | 2 | text   | 10/04/2021  00:00:00 |
|  5 |  | 2 | text   | 10/03/2021  00:00:00 |
|  6 |  | 3 | text   | 10/01/2021  00:00:00 |
+----+--+---+--------+----------------------+

Expected output

+-----+------------+
| avg |    date    |
+-----+------------+
|   2 | 10/04/2021 |
| 1,5 | 10/03/2021 |
|   1 | 10/01/2021 |
+-----+------------+

How can I do this with SQL?

Answer

here is one way :

select insert_date,avg(vv) from 
(
select cast(insert_date as Date) as insert_date, count(*) vv
from table 
group by cast(insert_date as Date), user
) tt
group by insert_date

Advertisement

Answer