Count occurrence of digits and using a case expression in a stored procedure

I want to write a stored procedure that displays the level of activity that an author has when it comes to create posts on a blog.

I want to input a username in the call statement like call activity_level('Ugrob'); and get the following result

nickname	level_activity	aupo_auth_id	author_id	time_posting
Ugrob	high	1	1	2003-02-05 13:28:57

I have the following tables

create table author_and_post(
aupo_post_id int,
aupo_auth_id int
);

create table author_info(
nickname varchar(16) primary key,
author_id int
);

create table posts(
post_id int,
time_posting datetime
);

Data for table author_and_post

aupo_post_id	aupo_auth_id
1	1
2	1
3	1
4	1
5	1
6	1
7	2
8	2
9	2
10	3
11	3

data for table author_info

nickname	author_id
Ugrob	1
Laeris	2
Summona	3

data for table posts

post_id	time_posting
1	2003-02-05 13:28:57
2	2003-02-06 21:30:57
3	2003-03-06 11:36:31
4	2003-03-06 11:37:31
5	2004-03-06 16:36:31
6	2005-03-06 11:36:31
7	2006-03-06 11:49:31
8	2007-03-06 11:11:34
9	2008-03-06 11:21:31
10	2009-03-06 11:44:31
11	2010-03-06 11:54:33

I have written the following code

drop procedure if exists level_activity; 
create procedure level_activity(user_insert varchar(16)) 
begin
 select nickname, count(*), aupo_auth_id, author_id, post_id, aupo_post_id, time_posting, 
 case
     when aupo_auth_id between 1 and 2 then 'low'
     when aupo_auth_id between 3 and 5 then 'medium'
     when aupo_auth_id > 5 then 'high' end as level_activity
from author_and_post join author_info 
on author_id = aupo_auth_id join posts on post_id = aupo_post_id
where nickname = user_insert
group by nickname, aupo_auth_id, author_id, post_id, aupo_post_id
end;
call activity_level('Lorry');

I’m getting

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘end’ at line 12

I wonder how to resolve this error and get the result I want. I use MySQL version 8.0.23

Answer

There are couple of issues. First, how did you decide on the time? There are 6 timestamps and there are 6 posts for the author ‘Ugrob’. Looking at your output it looks like max. If so, you need to specify that in your select.

Second, You need the count of the posts to determine level_activity and not the auth_id. So you would add that to your case.

All the columns in your select should be part of the group by or aggregate function. So you need to remove post_id from your select.

Your select would look something like below.

 select nickname, aupo_auth_id, author_id, max(time_posting), count(aupo_post_id),
case 
     when count(aupo_post_id) between 1 and 2 then 'low'
     when count(aupo_post_id) between 3 and 5 then 'medium'
     else 'high' 
     end as level_activity
from author_and_post join author_info 
on author_id = aupo_auth_id join posts on post_id = aupo_post_id
where nickname = 'Ugrob'
group by nickname, aupo_auth_id, author_id