Count of distinct columns alongside each record of distinct value of that column

I am new to SQL and am not sure if this is the right approach, but basically I am trying to get the count of distinct dates and put that beside the individual date

For eg

Date     Username Count(Distinct(Date))
20220721 jbravo    3
20220722 jbravo    3
20220723 jbravo    3
20220721 mario    7
20220722 mario    7
20220723 mario    7
20220724 mario    7 
20220725 mario    7
20220726 mario    7
20220727 mario    7

I know how to get count(distinct(date)) grouped by user, but how do I achieve the below since, if I do

select Date, Username, Count(Distinct(Date))
from table
group by Username

will throw an error that I need Date in the group by clause

If I add Date in the group by clause, obviously the distinct count becomes 1

Solution

That is an aggregation issue. You can use common table expression

WITH t1 AS (
  SELECT username, COUNT(DISTINCT(dates)) AS dist_dates
  FROM tab1
  GROUP BY username
  )
SELECT tab1.dates, tab1.username, t1.dist_dates
FROM tab1
INNER JOIN t1
ON t1.username = tab1.username

You should be aware that the best option would be joining on ids. However, since you did not mention you have that, this solution works fine.