MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE `x_badges` (
  `xb_ID` int(11) NOT NULL,
  `xb_requirement` varchar(40) NOT NULL,
  `xb_requirement_value` int(11) NOT NULL,
  `xb_text` text NOT NULL,
  `xb_text_details` text NOT NULL,
  `xb_icon` varchar(20) NOT NULL,
  `xb_color` varchar(6) NOT NULL,
  `xb_color_text` varchar(6) NOT NULL,
  `xb_level_name` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `x_badges` (`xb_ID`, `xb_requirement`, `xb_requirement_value`, `xb_text`, `xb_text_details`, `xb_icon`, `xb_color`, `xb_color_text`, `xb_level_name`) VALUES
(11, 'added_interaction', 10, 'Added interactions', 'Received more than %d points adding interactions with target companies!', 'search-dollar', 'cc8e34', '', 'Bronze'),
(15, 'added_target', 10, 'Added targets', 'Received more than %s points adding target companies!', 'dollar-sign', 'cc8e34', '', 'Bronze'),
(16, 'asked_question', 10, 'Asked questions', 'Received more than %s points asking questions!', 'question', 'cc8e34', '', 'Bronze'),
(17, 'notified_colleagues_interaction', 10, 'Interactions shared with colleagues', 'Received more than %s points tagging colleagues in interactions added!', 'bullhorn', 'cc8e34', '', 'Bronze'),
(18, 'reply_accepted', 10, 'Helpful replies', 'Received more than %s points thanks to replies marked as helpful!', 'check-double', 'cc8e34', '', 'Bronze'),
(19, 'reply_question', 10, 'Replies to questions', 'Received more than %s points replying to questions!', 'comments', 'cc8e34', '', 'Bronze'),
(20, 'updated_info', 10, 'Updated target profiles', 'Received more than %s points updating profiles of targets!', 'funnel-dollar', 'cc8e34', '', 'Bronze'),
(21, 'updated_wiki', 10, 'Updated wiki', 'Received more than %s points updating profiles of Group companies!', 'pen-nib', 'cc8e34', '', 'Bronze'),
(22, 'upvote_question', 10, 'Helpful questions', 'Received more than %s points for having own questions upvoted!', 'grin-alt', 'cc8e34', '', 'Bronze'),
(23, 'added_interaction', 100, 'Added interactions', 'Received more than %s points adding interactions with target companies!', 'search-dollar', 'aaa9ad', '', 'Silver'),
(24, 'added_interaction', 500, 'Added interactions', 'Received more than %s points adding interactions with target companies!', 'search-dollar', 'ffd700', '495057', 'Gold'),
(25, 'updated_wiki', 1000, 'Updated Bertelsmann wiki', 'Received more than %s points asking questions!', 'pen-nib', 'b9f2ff', '495057', 'Diamond'),
(26, 'added_interaction', 1000, 'Added interactions', 'Received more than %s points adding interactions with target companies!', 'search-dollar', 'b9f2ff', '495057', 'Diamond'),
(27, 'added_interaction', 5000, 'Added interactions', 'Received more than %s points adding interactions with target companies!', 'search-dollar', 'e0115f', '', 'Ruby');

SELECT * FROM x_badges;


xb_ID
xb_requirement
xb_requirement_value
xb_text
xb_text_details
xb_icon
xb_color
xb_color_text
xb_level_name


11
added_interaction
10
Added interactions
Received more than %d points adding interactions with target companies!
search-dollar
cc8e34

Bronze

15
added_target
10
Added targets
Received more than %s points adding target companies!
dollar-sign
cc8e34

Bronze

16
asked_question
10
Asked questions
Received more than %s points asking questions!
question
cc8e34

Bronze

17
notified_colleagues_interaction
10
Interactions shared with colleagues
Received more than %s points tagging colleagues in interactions added!
bullhorn
cc8e34

Bronze

18
reply_accepted
10
Helpful replies
Received more than %s points thanks to replies marked as helpful!
check-double
cc8e34

Bronze

19
reply_question
10
Replies to questions
Received more than %s points replying to questions!
comments
cc8e34

Bronze

20
updated_info
10
Updated target profiles
Received more than %s points updating profiles of targets!
funnel-dollar
cc8e34

Bronze

21
updated_wiki
10
Updated wiki
Received more than %s points updating profiles of Group companies!
pen-nib
cc8e34

Bronze

22
upvote_question
10
Helpful questions
Received more than %s points for having own questions upvoted!
grin-alt
cc8e34

Bronze

23
added_interaction
100
Added interactions
Received more than %s points adding interactions with target companies!
search-dollar
aaa9ad

Silver

24
added_interaction
500
Added interactions
Received more than %s points adding interactions with target companies!
search-dollar
ffd700
495057
Gold

25
updated_wiki
1000
Updated Bertelsmann wiki
Received more than %s points asking questions!
pen-nib
b9f2ff
495057
Diamond

26
added_interaction
1000
Added interactions
Received more than %s points adding interactions with target companies!
search-dollar
b9f2ff
495057
Diamond

27
added_interaction
5000
Added interactions
Received more than %s points adding interactions with target companies!
search-dollar
e0115f

Ruby


CREATE TABLE `x_badges_earned` (
  `xbe_ID` int(11) NOT NULL,
  `xbe_uID` int(11) NOT NULL,
  `xbe_bID` int(11) NOT NULL,
  `xbe_timestamp` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `x_badges_earned` (`xbe_ID`, `xbe_uID`, `xbe_bID`, `xbe_timestamp`) VALUES
(19, 1, 11, '2020-03-23 15:24:54'),
(20, 1, 15, '2020-03-23 15:24:54'),
(21, 1, 16, '2020-03-23 15:24:54'),
(22, 1, 17, '2020-03-23 15:24:54'),
(23, 1, 18, '2020-03-23 15:24:54'),
(24, 1, 19, '2020-03-23 15:24:54'),
(25, 1, 23, '2020-03-23 16:00:51'),
(26, 1, 20, '2020-03-23 15:32:31'),
(27, 1, 21, '2020-03-23 15:32:31'),
(28, 1, 22, '2020-03-23 15:32:31'),
(29, 10, 25, '2020-03-23 15:37:32');

SELECT * FROM x_badges_earned;


xbe_ID
xbe_uID
xbe_bID
xbe_timestamp


19
1
11
2020-03-23 15:24:54

20
1
15
2020-03-23 15:24:54

21
1
16
2020-03-23 15:24:54

22
1
17
2020-03-23 15:24:54

23
1
18
2020-03-23 15:24:54

24
1
19
2020-03-23 15:24:54

25
1
23
2020-03-23 16:00:51

26
1
20
2020-03-23 15:32:31

27
1
21
2020-03-23 15:32:31

28
1
22
2020-03-23 15:32:31

29
10
25
2020-03-23 15:37:32


SET @user := 1;
-- Get all earned badges (including over-earned)
SELECT *
FROM x_badges xb
JOIN x_badges_earned xbe ON xb.xb_ID = xbe.xbe_bID
WHERE xbe.xbe_uID = @user;


xb_ID
xb_requirement
xb_requirement_value
xb_text
xb_text_details
xb_icon
xb_color
xb_color_text
xb_level_name
xbe_ID
xbe_uID
xbe_bID
xbe_timestamp


11
added_interaction
10
Added interactions
Received more than %d points adding interactions with target companies!
search-dollar
cc8e34

Bronze
19
1
11
2020-03-23 15:24:54

15
added_target
10
Added targets
Received more than %s points adding target companies!
dollar-sign
cc8e34

Bronze
20
1
15
2020-03-23 15:24:54

16
asked_question
10
Asked questions
Received more than %s points asking questions!
question
cc8e34

Bronze
21
1
16
2020-03-23 15:24:54

17
notified_colleagues_interaction
10
Interactions shared with colleagues
Received more than %s points tagging colleagues in interactions added!
bullhorn
cc8e34

Bronze
22
1
17
2020-03-23 15:24:54

18
reply_accepted
10
Helpful replies
Received more than %s points thanks to replies marked as helpful!
check-double
cc8e34

Bronze
23
1
18
2020-03-23 15:24:54

19
reply_question
10
Replies to questions
Received more than %s points replying to questions!
comments
cc8e34

Bronze
24
1
19
2020-03-23 15:24:54

20
updated_info
10
Updated target profiles
Received more than %s points updating profiles of targets!
funnel-dollar
cc8e34

Bronze
26
1
20
2020-03-23 15:32:31

21
updated_wiki
10
Updated wiki
Received more than %s points updating profiles of Group companies!
pen-nib
cc8e34

Bronze
27
1
21
2020-03-23 15:32:31

22
upvote_question
10
Helpful questions
Received more than %s points for having own questions upvoted!
grin-alt
cc8e34

Bronze
28
1
22
2020-03-23 15:32:31

23
added_interaction
100
Added interactions
Received more than %s points adding interactions with target companies!
search-dollar
aaa9ad

Silver
25
1
23
2020-03-23 16:00:51


-- Get most expensive badges identifying parameters
SELECT xb.xb_icon, MAX(xb.xb_requirement_value) xb_requirement_value
FROM x_badges xb
JOIN x_badges_earned xbe ON xb.xb_ID = xbe.xbe_bID
WHERE xbe.xbe_uID = @user
GROUP BY xb_icon;


xb_icon
xb_requirement_value


search-dollar
100

dollar-sign
10

question
10

bullhorn
10

check-double
10

comments
10

funnel-dollar
10

pen-nib
10

grin-alt
10


-- Get most expensive badges
SELECT *
FROM x_badges xb
JOIN x_badges_earned xbe ON xb.xb_ID = xbe.xbe_bID
JOIN ( SELECT xb.xb_icon, MAX(xb.xb_requirement_value) xb_requirement_value
       FROM x_badges xb
       JOIN x_badges_earned xbe ON xb.xb_ID = xbe.xbe_bID
       WHERE xbe.xbe_uID = @user
       GROUP BY xb_icon ) max_earned ON (xb.xb_icon, xb.xb_requirement_value) 
                                      = (max_earned.xb_icon, max_earned.xb_requirement_value)
WHERE xbe.xbe_uID = @user;


xb_ID
xb_requirement
xb_requirement_value
xb_text
xb_text_details
xb_icon
xb_color
xb_color_text
xb_level_name
xbe_ID
xbe_uID
xbe_bID
xbe_timestamp
xb_icon
xb_requirement_value


15
added_target
10
Added targets
Received more than %s points adding target companies!
dollar-sign
cc8e34

Bronze
20
1
15
2020-03-23 15:24:54
dollar-sign
10

16
asked_question
10
Asked questions
Received more than %s points asking questions!
question
cc8e34

Bronze
21
1
16
2020-03-23 15:24:54
question
10

17
notified_colleagues_interaction
10
Interactions shared with colleagues
Received more than %s points tagging colleagues in interactions added!
bullhorn
cc8e34

Bronze
22
1
17
2020-03-23 15:24:54
bullhorn
10

18
reply_accepted
10
Helpful replies
Received more than %s points thanks to replies marked as helpful!
check-double
cc8e34

Bronze
23
1
18
2020-03-23 15:24:54
check-double
10

19
reply_question
10
Replies to questions
Received more than %s points replying to questions!
comments
cc8e34

Bronze
24
1
19
2020-03-23 15:24:54
comments
10

20
updated_info
10
Updated target profiles
Received more than %s points updating profiles of targets!
funnel-dollar
cc8e34

Bronze
26
1
20
2020-03-23 15:32:31
funnel-dollar
10

21
updated_wiki
10
Updated wiki
Received more than %s points updating profiles of Group companies!
pen-nib
cc8e34

Bronze
27
1
21
2020-03-23 15:32:31
pen-nib
10

22
upvote_question
10
Helpful questions
Received more than %s points for having own questions upvoted!
grin-alt
cc8e34

Bronze
28
1
22
2020-03-23 15:32:31
grin-alt
10

23
added_interaction
100
Added interactions
Received more than %s points adding interactions with target companies!
search-dollar
aaa9ad

Silver
25
1
23
2020-03-23 16:00:51
search-dollar
100

xb_ID	xb_requirement	xb_requirement_value	xb_text	xb_text_details	xb_icon	xb_color	xb_color_text	xb_level_name
11	added_interaction	10	Added interactions	Received more than %d points adding interactions with target companies!	search-dollar	cc8e34		Bronze
15	added_target	10	Added targets	Received more than %s points adding target companies!	dollar-sign	cc8e34		Bronze
16	asked_question	10	Asked questions	Received more than %s points asking questions!	question	cc8e34		Bronze
17	notified_colleagues_interaction	10	Interactions shared with colleagues	Received more than %s points tagging colleagues in interactions added!	bullhorn	cc8e34		Bronze
18	reply_accepted	10	Helpful replies	Received more than %s points thanks to replies marked as helpful!	check-double	cc8e34		Bronze
19	reply_question	10	Replies to questions	Received more than %s points replying to questions!	comments	cc8e34		Bronze
20	updated_info	10	Updated target profiles	Received more than %s points updating profiles of targets!	funnel-dollar	cc8e34		Bronze
21	updated_wiki	10	Updated wiki	Received more than %s points updating profiles of Group companies!	pen-nib	cc8e34		Bronze
22	upvote_question	10	Helpful questions	Received more than %s points for having own questions upvoted!	grin-alt	cc8e34		Bronze
23	added_interaction	100	Added interactions	Received more than %s points adding interactions with target companies!	search-dollar	aaa9ad		Silver
24	added_interaction	500	Added interactions	Received more than %s points adding interactions with target companies!	search-dollar	ffd700	495057	Gold
25	updated_wiki	1000	Updated Bertelsmann wiki	Received more than %s points asking questions!	pen-nib	b9f2ff	495057	Diamond
26	added_interaction	1000	Added interactions	Received more than %s points adding interactions with target companies!	search-dollar	b9f2ff	495057	Diamond
27	added_interaction	5000	Added interactions	Received more than %s points adding interactions with target companies!	search-dollar	e0115f		Ruby

xbe_ID	xbe_uID	xbe_bID	xbe_timestamp
19	1	11	2020-03-23 15:24:54
20	1	15	2020-03-23 15:24:54
21	1	16	2020-03-23 15:24:54
22	1	17	2020-03-23 15:24:54
23	1	18	2020-03-23 15:24:54
24	1	19	2020-03-23 15:24:54
25	1	23	2020-03-23 16:00:51
26	1	20	2020-03-23 15:32:31
27	1	21	2020-03-23 15:32:31
28	1	22	2020-03-23 15:32:31
29	10	25	2020-03-23 15:37:32