How to query for most prevalent weekday from datetime column?-CodePudding

Table:

ID	USER NAME	created_at
6	cleark makeng	'2017-04-30 13:26:14'
7	Khutikjh_Homhuik	'2016-12-12 06:50:08'
8	Tabithah hamberger11	'2016-08-20 02:19:46'
9	Guy99	'2016-06-24 19:36:31'
10	Preslis Mablure	'2016-08-07 16:25:49'

How to write an SQL query to calculate weekday most users created an account?

CodePudding user response：

If you are using mysql,then we can using WEEK() to convert it from datetime to week date,then group by week date

SELECT count(u.user_name) as cnt,u.wd
FROM
(
 SELECT user_name,WEEK(created_at) AS wd
 FROM yourtable
) u
GROUP BY u.wd
ORDER BY count(u.user_name) DESC
LIMIT 1

CodePudding user response：

try this if you want week day as you mentioned in question else use @lucumt answer.

SELECT week_day, count(week_day) FROM
(
    SELECT weekday(created_at) week_day
    FROM demodb.user
) temp
group by week_day LIMIT 1;

CodePudding user response：

For MySQL, this is just:

select dayname(created_at)
from mysterytablename
group by 1
order by count(*) desc
limit 1