Get most voted time/active time from countries in mysql

Question

I've been working on a poll application. Where I want to get the most active/voting time of all countries for a certain poll.

here all the necessary table structure.

countries table
+---------+-----------+
| id      | question  |
+---------+-----------+
| 1       | Bangladesh|
| 2       | India     |
| 3       | Pakistan  |
| 4       | Canada    |
| 5       | Norway    |
| 6       | Srilanka  |
+---------+-----------+

polls table
+---------+-----------+
| id      | question  |
+---------+-----------+
| 1       | question1 |
| 2       | question2 |
| 3       | question3 |
| 4       | question4 |
+---------+-----------+

votes table
+---------+-----------+------------+----------------------+
| id      | poll_id   | country_id |      created_at      |
+---------+-----------+------------+----------------------+
| 1       |     3     |     3      | 2019-12-05 01:20:29  |
| 2       |     1     |     1      | 2019-12-05 02:30:35  |
| 3       |     2     |     3      | 2019-13-05 15:10:49  |
| 4       |     2     |     1      | 2019-13-05 18:20:46  |
| 5       |     3     |     2      | 2019-14-05 10:49:44  |
| 6       |     3     |     3      | 2019-14-05 11:05:26  |
| 7       |     1     |     4      | 2019-14-05 13:30:11  |
| 8       |     3     |     5      | 2019-15-05 21:40:33  |
+---------+-----------+------------+----------------------+

what I actually want to get is like following

votes table
+---------+-----------+-----------------+
| poll_id | country_id| most_voted_time |
+---------+-----------+-----------------+
| 1       |     1     |     Evening     |
| 1       |     2     |     Morning     |
| 1       |     3     |  Early Morning  |
| 1       |     4     |       Night     |
| 2       |     1     |     Morning     |
| 2       |     2     |   Late Morning  |
| 2       |     3     |     Morning     |
| 2       |     4     |     Morning     |
+---------+-----------+-----------------+

Here Morning (5am-10.30am),Late Morning (10.30am-11.59am), Afternoon(12pm-430pm) , Late Afternoon(4,31pm- 5pm) Evening (5pm to 8.59pm), Night (9pm- 11.59pm, Early Morning (1200am- 4.59am)

I'll be so grateful for your answer. Pardon me for my typos or mistakes

Answer 1

Use a CTE that associate times with names then join with votes

with cte_range (hfrom, hto, label) as ( 
values   ('5:00', '10:30', 'Morning'), 
         ('10:30', '11:59', 'Late Morning'), 
         ('12:00', '16:30', 'Afternoon'), 
         ('16:31', '17:00', 'Late Afternoon'), 
         ('17:00', '20:59', 'Evening'), 
         ('21:00', '23:59', 'Night'), 
         ('12:00', '4:59', 'Early Morning') 
) 
select v.poll_id, v.country_id, r.label 
from votes v 
join cte_range r 
ON DATE_FORMAT(v.created_at,'%H:%i') between r.hfrom and r.hto 
group by v.poll_id, v.country_id 
order by v.poll_id;