SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table Users
(
Mail nvarchar (20) primary key ,
Password nvarchar (8)  not null,
FirstName nvarchar (20) not null,
LastName nvarchar (20) not null,
BirthDate date  not null,
JoinDate date not null,
Gender nchar(1) ,
NickName nvarchar(20),
Photo nvarchar(20),
Phone bigint  not null
)

INSERT INTO Users
VALUES
('Kelly@gmail.com','k1000000','Kelly','Ka','1992-05-15','2016-09-04','F','Kelly','Kelly.jpg','546296100'),
('Lilly@gmail.com','l1101111','Lilly','La','1999-04-03','2012-04-04','F','Lilly','Lilly.jpg','542448300'),
('Nelly@gmail.com','n130131','Nelly','Na','1994-03-07','2020-04-13','F','Nelly','NellyNa.jpg','541234567'),
('Owen@gmail.com','o140141','Owen','Oa','1992-02-02','2020-05-13','M','Owen','OwenOa.jpg','541234567');
4 rows affected

create table Post
(
ID int identity(1,1) primary key,
Photo nvarchar(20),
Text nvarchar(200),
Location nvarchar(50),
Video int,
DatePosted date ,
UserMail nvarchar (20) 
)

INSERT INTO Post
VALUES
('','my name is nellu','','','2020-05-08','Nelly@gmail.com'),
('','hii','','','2020-02-19','Lilly@gmail.com');
2 rows affected

create table Tagging
(
Mail nvarchar (20)  not null,
IDPost int  not null,    
TagMail nvarchar(20)  not null,
primary key (Mail,IDPost)
)

INSERT INTO Tagging
VALUES
('Nelly@gmail.com','1','Kelly@gmail.com'),
('Nelly@gmail.com','2','Owen@gmail.com');
2 rows affected

     SELECT T.Mail,COUNT(P.ID) as myCount
     FROM Post as P
        INNER JOIN Tagging as T 
            ON T.IDPost = P.ID
     ---WHERE datediff(day,P.DatePosted,getdate()) <= 30
     GROUP BY T.Mail


Mail
myCount


Nelly@gmail.com
2


select U.Mail, p.myCount as PostCount, 
  case 
    when p.myCount = 0 then 'none'
        when p.myCount <= 2 then 'few'
        when p.myCount <= 5 then 'medium'
        when p.myCount > 5 then 'many'
    else 'N/A'
  end PostCountCategory
from Users U 
    --left join Tagging T on U.Mail = T.Mail
    left join 
    (
     SELECT T.Mail,COUNT(P.ID) as myCount
     FROM Post as P
        INNER JOIN Tagging as T 
            ON T.IDPost = P.ID
     WHERE datediff(day,P.DatePosted,getdate()) <= 30
     GROUP BY T.Mail
     )as p 
        ON P.Mail = U.Mail
--where datediff(day,P.DatePosted,getdate()) <= 30 --Because of this condition I would never get 'none'
--group by U.Mail, U.Gender


Mail
PostCount
PostCountCategory


Kelly@gmail.com
null
N/A

Lilly@gmail.com
null
N/A

Nelly@gmail.com
null
N/A

Owen@gmail.com
null
N/A
Mail	PostCount	PostCountCategory
Kelly@gmail.com	null	N/A
Lilly@gmail.com	null	N/A
Nelly@gmail.com	null	N/A
Owen@gmail.com	null	N/A