How to write sql query to calculate which day of a week, most user created their account from a date-CodePudding

dataset in given like this.

`**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'**
`

There is n number of columns, How to write SQL query to calculate which day of a week, most user created their account from this datetime column ?

time & date of a week.

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：

This is just:

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